|
发表于 2014-11-29 10:04
|
显示全部楼层
本楼为最佳答案
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "B6" Then
With Sheets("数据源")
ROW1 = .Range("A" & .Rows.Count).End(xlUp).Row
ARR1 = .Range("A2:R" & ROW1)
End With
If ROW1 > 1 Then
ReDim ARR11(1 To UBound(ARR1), 1 To 8)
For I = 1 To UBound(ARR1)
If ARR1(I, 7) = Target.Value And ARR1(I, 13) <> 0 Then
N = N + 1
ARR11(N, 1) = N
ARR11(N, 2) = Year(Now()) & "-" & ARR1(I, 2)
ARR11(N, 3) = ARR1(I, 3)
ARR11(N, 4) = ARR1(I, 8)
ARR11(N, 5) = ARR1(I, 9)
ARR11(N, 6) = ARR1(I, 15)
ARR11(N, 7) = ARR1(I, 13)
ARR11(N, 8) = ARR1(I, 16)
QTFY = QTFY + ARR11(N, 6)
ZZL = ZZL + ARR11(N, 5)
YF = YF + ARR11(N, 7)
HJ = HJ + ARR11(N, 6) + ARR11(N, 7)
End If
Next I
With Sheets("想要的示例A")
If N > 0 Then
.Range("A8:H8").ClearContents
.Range("A9:H" & .Rows.Count).Clear
.Range("A8").Resize(N, 8) = ARR11
If N > 1 Then
.Range("A8:H8").Copy
.Range("A9:H" & 8 + N - 1).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End If
.Range("B" & 8 + N) = "其它费用:"
.Range("D" & 8 + N) = "总重量:"
.Range("B" & 8 + N + 1) = "运费:"
.Range("B" & 8 + N + 2) = "TOTAL:"
.Range("C" & 8 + N) = QTFY
.Range("E" & 8 + N) = ZZL
.Range("C" & 8 + N + 1) = YF
.Range("C" & 8 + N + 2) = HJ
.Range("C" & 8 + N + 2).Select
Else
.Range("A8:H8").ClearContents
.Range("A9:H" & .Rows.Count).Clear
End If
End With
End If
End If
End Sub |
|