对两个Excel表进行加减计算，输出到sheet2中（VBA按钮实现）计算可用库存

zz1180

对两个Excel表进行加减计算，输出到sheet2中（VBA按钮实现）计算可用库存

1. Sub 汇总计算()
   
2. Application.ScreenUpdating = False
   
3. Dim wb, wb1 As Workbook
   
4. Dim arr, brr, crr As Variant
   
5. Dim i, k As Integer
   
6. t = Timer
   
7. Worksheets("模板").UsedRange.ClearContents
   
8. Worksheets("模板").[a1].Resize(1, 6) = Array("产品ID", "产品名称", "产品编码", "库存数量", "预领用数量", "可用库存")
   
9. Set wb = Workbooks.Open(ThisWorkbook.Path & "\库存表.xls")
   
10. crr = wb.Worksheets("库存表").[a1].CurrentRegion.Offset(1)
    
11. ThisWorkbook.Worksheets("模板").[a2].Resize(UBound(crr), UBound(crr, 2)) = crr
    
12. wb.Close False
    
13. arr = ThisWorkbook.Worksheets("模板").[a1].CurrentRegion
    
14. Set wb1 = Workbooks.Open(ThisWorkbook.Path & "\领用表.xls")
    
15. brr = wb1.Worksheets("领用表").[a1].CurrentRegion
    
16. wb1.Close False
    
17. For i = 2 To UBound(arr)
    
18. For k = 2 To UBound(brr)
    
19. If arr(i, 1) = brr(k, 2) Then
    
20. arr(i, 5) = brr(k, 4)
    
21. End If
    
22. If Len(arr(i, 5)) = 0 Then
    
23. arr(i, 5) = 0
    
24. Else
    
25. arr(i, 6) = Val(arr(i, 4)) - Val(arr(i, 5))
    
26. End If
    
27. Next k
    
28. Next i
    
29. ThisWorkbook.Worksheets(2).Select
    
30. ThisWorkbook.Worksheets(2).[a1].Resize(UBound(arr), UBound(arr, 2)) = arr   '输出到
    
31. MsgBox "OK!，汇总计算完毕，耗时" & Format(Timer - t, "00"), 64, "温馨提示"
    
32. Application.ScreenUpdating = True
    
33. End Sub

复制代码

dbjxnsy

学习一下

dd0916

☆★☆㊣☆★☆㊣☆★☆
【下載狀況】OK
【檔案狀況】OK
☆★☆㊣☆★☆㊣☆★☆

wulunting

你这代码很好，能不能上传个附件，让我学学。