|
如下請測試看看,謝謝
再问:是用事件更新好,还是用按钮好呢?数据大概会上万条以上
>> 資料多時要用按鈕,謝謝
Sub test()
Dim Arr, xD, T$, Crr(1 To 1, 1 To 3), Brr
Set xD = CreateObject("Scripting.Dictionary")
Arr = Sheets(2).[a1].CurrentRegion
ReDim Brr(1 To UBound(Arr), 1 To 4)
For i = 2 To UBound(Arr)
T = Arr(i, 3)
If Arr(i, 4) = "签客户" Then
If xD.Exists(T) Then
m = xD(T): Brr(m, 2) = Brr(m, 2) + Arr(i, 9)
Brr(m, 3) = Brr(m, 3) + Arr(i, 10)
Brr(m, 4) = Brr(m, 4) + Arr(i, 9) - Arr(i, 10)
Crr(1, 1) = Crr(1, 1) + Arr(i, 9)
Crr(1, 2) = Crr(1, 2) + Arr(i, 10)
Crr(1, 3) = Crr(1, 3) + Arr(i, 10)
Else
n = n + 1: xD(T) = n
Brr(n, 1) = Arr(i, 3): Brr(n, 2) = Arr(i, 9)
Brr(n, 3) = Arr(i, 10): Brr(n, 4) = Arr(i, 9) - Arr(i, 10)
For j = 1 To 3: Crr(1, j) = Crr(1, j) + Brr(n, j + 1): Next
End If
End If
Next
Sheets(1).[h5].Resize(n, 4) = Brr
Sheets(1).[i3].Resize(1, 3) = Crr
End Sub
|
|