|
本帖最后由 dequang 于 2019-2-19 17:17 编辑
服务器又上传不了图片了
相应在地方改成它,运行速度更快。
Sub 录入单价及金额()
'字典、数组结合
On Error Resume Next
Sheets("4月统计").Range("e2:f" & Range("a" & Rows.Count).End(xlUp).Row).ClearContents
Set d = CreateObject("scripting.dictionary")
Dim arr, brr
With Sheets("工价表")
arr = .Range("a1:c" & .Range("a" & Rows.Count).End(xlUp).Row)
End With
With Sheets("4月统计")
brr = .Range("b2:f" & .Range("b" & Rows.Count).End(xlUp).Row)
End With
For i = 2 To UBound(arr)
'关键字最后一次出现所在的行,可随时在工价表最后一行添加数据,不管前面的产品规格有没有出现过,单价以最后一次为准
d(arr(i, 1) & arr(i, 2)) = i
'关键字第一次出现所在的行,按需选择
'd.Add arr(i, 1) & arr(i, 2), i
Next
For i = 1 To UBound(brr)
If d(brr(i, 1) & brr(i, 2)) = False Then
brr(i, 4) = "在工价表里找不到该型号,请检查"
Else
k = d(brr(i, 1) & brr(i, 2))
brr(i, 4) = arr(k, 3)
brr(i, 5) = brr(i, 3) * brr(i, 4)
End If
Next
[e2].Resize(UBound(brr), 1) = WorksheetFunction.Index(brr, 0, 4)
[f2].Resize(UBound(brr), 1) = WorksheetFunction.Index(brr, 0, 5)
Set d = Nothing
End Sub |
|