点击文件名下载附件
昨天的问题已解决,但是现在又有个问题
Private Sub Worksheet_Activate() Dim Dic As New Dictionary Dim Arr, CK, i&, dR_n&, aR_n&, S$, N%, k& Dim CM(1 To 4) As Long On Error GoTo Err_Exit With Sheet1 dR_n = .[d65536].End(xlUp).Row Arr = .Range("D2:G" & dR_n) End With For i = 1 To UBound(Arr) S = Arr(i, 1) & "-" & Arr(i, 2) N = Arr(i, 3) / 5 - 21 If Dic.Exists(S) Then CM(N) = CM(N) + Arr(i, 4) Dic(S) = CM Else CM(N) = Arr(i, 4) Dic(S) = CM End If Next With Sheet2 dR_n = .[d65536].End(xlUp).Row Arr = .Range("D2:G" & dR_n) End With For i = 1 To UBound(Arr) S = Arr(i, 1) & "-" & Arr(i, 2) N = Arr(i, 3) / 5 - 21 If Dic.Exists(S) Then CK = Dic(S) CK(N) = CK(N) - Arr(i, 4) Dic(S) = CK Else MsgBox Arr(i, 3) & "无库存!" Exit Sub End If Next aR_n = [a65536].End(xlUp).Row Range("A2:I" & aR_n).ClearContents Range("A2:I" & aR_n).Interior.ColorIndex = xlNone Range("A2:I" & aR_n).Borders.LineStyle = xlNone For i = 0 To Dic.Count - 1 k = i + 2 Cells(k, 1).Resize(1, 2) = Split(Dic.Keys(i), "-") Cells(k, 3) = "件" Cells(k, "E").Resize(1, 4) = Dic.Items(i) Cells(k, "I") = Application.Sum(Dic.Items(i)) Next Cells(k + 1, 1) = "合计" Range("A2:A" & k).Interior.ColorIndex = 44 Range("E2:I" & k).Interior.ColorIndex = 34 Range("A2:I" & k).Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin, DataOption1:= _ xlSortNormal, DataOption2:=xlSortNormal Range("A2:I" & k).Borders.LineStyle = xlContinuous Err_Exit: Exit Sub End Sub