[已解决]根据名称提取单价

salt

如何用宏根据物资结算表里的物料名称从单价表里导入单价信息后合计金额，我对VBA是菜鸟，求老师们帮帮忙写个代码，万分感谢！！

最佳答案

月排行榜 / 总排行榜

dsmch

2014-12-22 13:10

………………

跳转到最佳答案楼层

baksy

函数解决

dsmch

1. Sub Macro1()
   
2. Dim arr, brr, crr, wb As Workbook, d, i&
   
3. Set d = CreateObject("scripting.dictionary")
   
4. arr = Range("a1").CurrentRegion
   
5. ReDim crr(1 To UBound(arr) - 2, 1 To 2)
   
6. Application.ScreenUpdating = False
   
7. Set wb = GetObject(ThisWorkbook.Path & "\单价表.xls")
   
8. brr = wb.Sheets(1).Range("a1").CurrentRegion
   
9. wb.Close 0
   
10. For i = 6 To UBound(brr)
    
11.     zf = brr(i, 2) & "," & brr(i, 3) & "," & brr(i, 4)
    
12.     d(zf) = brr(i, 6)
    
13. Next
    
14. For i = 3 To UBound(arr)
    
15.     zf = arr(i, 2) & "," & arr(i, 3) & "," & arr(i, 4)
    
16.     If d.exists(zf) Then
    
17.         crr(i - 2, 1) = d(zf)
    
18.         crr(i - 2, 2) = arr(i, 7) * d(zf)
    
19.     End If
    
20. Next
    
21. Range("h3").Resize(UBound(crr), 2) = crr
    
22. Application.ScreenUpdating = True
    
23. End Sub

复制代码

dsmch

………………