|
本帖最后由 wuxiuyi 于 2017-7-19 16:54 编辑
本人新手,写VBA总是出错(悲伤),还不知道错在哪儿……希望路过的前辈帮忙看看,感恩
想实现的功能是能够得出每周的长江均价(还要自动刨掉休假日)。
之前在VBA里调用Vlookup和Match函数,但速度很慢,就想改用字典和数组,但提示各种错误……
代码如下:
Sub CJaverage()
Application.ScreenUpdating = False
Dim d1, d2
Dim Arr, Brr, ir%, Myr%, x%, y%
Dim UsedRowCJ As Integer
Dim UsedRowRange As Integer
Dim i%, n%
Dim FirstRow As Integer
Dim FirstDate As Double
Dim LastDate As Double
Dim t As Double
Dim ss As String
Set d1 = CreateObject("Scripting.Dictionary")
With Sheets("Price")
ir = .Cells(Rows.Count, 3).End(xlUp).Row
Arr = .range("C2:G" & ir)
For x = 1 To UBound(Arr)
d1(Arr(x, 1)) = Arr(x, 5)
Next
End With
With Sheets("Average")
UsedRowCJ = .Cells(Rows.Count, 5).End(xlUp).Row
UsedRowRange = .Cells(Rows.Count, 4).End(xlUp).Row
Set d2 = CreateObject("Scripting.Dictionary")
Myr = .Cells(Rows.Count, 3).End(xlUp).Row
Brr = .range("C2:C" & Myr)
For y = 1 To UBound(Brr)
d2(Brr(y, 1)) = y + 1
Next
For i = (UsedRowCJ + 1) To UsedRowRange Step 1
If d2.exists(Cells(i, 4)) Then FisrtRow = d2(Cells(i, 4)).Value
FirstDate = .range("A" & FirstRow).Value2
LastDate = .range("B" & FirstRow).Value2
n = 0
For t = FirstDate To LastDate
If d1.exists(t) Then
d1("ss") = d1("ss") + d1(t)
n = n + 1
End If
Next t
range("E" & i) = d1("ss") / n
Next i
End With
Application.ScreenUpdating = True
End Sub
本帖最后由 大灰狼1976 于 2017-7-19 14:50 编辑
这一句错误,修改如下:
有几个问题,其中一个firstrow写成了fisrtrow,声明变量时用大小写主要是用来防止写错的,
你代码中编写变量时最好只用小写,如果首字母变成大写,说明变量没有写错,反之就要修改,
你这样一来,都不好查找错误。
If d2.exists(.Cells(i, 4).Value) Then FirstRow = d2(.Cells(i, 4).Value)
|
|