对两个Excel表进行加减计算，输出到sheet2中（VBA按钮实现）计算可用库存
对两个Excel表进行加减计算，输出到sheet2中（VBA按钮实现）计算可用库存
Sub 汇总计算()
Application.ScreenUpdating = False
Dim wb, wb1 As Workbook
Dim arr, brr, crr As Variant
Dim i, k As Integer
t = Timer
Worksheets("模板").UsedRange.ClearContents
Worksheets("模板")..Resize(1, 6) = Array("产品ID", "产品名称", "产品编码", "库存数量", "预领用数量", "可用库存")
Set wb = Workbooks.Open(ThisWorkbook.Path & "\库存表.xls")
crr = wb.Worksheets("库存表")..CurrentRegion.Offset(1)
ThisWorkbook.Worksheets("模板")..Resize(UBound(crr), UBound(crr, 2)) = crr
wb.Close False
arr = ThisWorkbook.Worksheets("模板")..CurrentRegion
Set wb1 = Workbooks.Open(ThisWorkbook.Path & "\领用表.xls")
brr = wb1.Worksheets("领用表")..CurrentRegion
wb1.Close False
For i = 2 To UBound(arr)
For k = 2 To UBound(brr)
If arr(i, 1) = brr(k, 2) Then
arr(i, 5) = brr(k, 4)
End If
If Len(arr(i, 5)) = 0 Then
arr(i, 5) = 0
Else
arr(i, 6) = Val(arr(i, 4)) - Val(arr(i, 5))
End If
Next k
Next i
ThisWorkbook.Worksheets(2).Select
ThisWorkbook.Worksheets(2)..Resize(UBound(arr), UBound(arr, 2)) = arr '输出到
MsgBox "OK!，汇总计算完毕，耗时" & Format(Timer - t, "00"), 64, "温馨提示"
Application.ScreenUpdating = True
End Sub
{:1_1:}学习一下 ☆★☆㊣☆★☆㊣☆★☆
【下載狀況】OK
【檔案狀況】OK
☆★☆㊣☆★☆㊣☆★☆ 你这代码很好，能不能上传个附件，让我学学。
页:
[1]