- ©«¤l
- 438
- ¥DÃD
- 67
- ºëµØ
- 0
- ¿n¤À
- 531
- ÂI¦W
- 30
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-10-30
- ³Ì«áµn¿ý
- 2024-10-24
|
¦^´_ 4# Andy2483
§Úקï¤F¦p¤U¡Aº¡¨¬¤F©Ò¦³n¨D¡C
Option Explicit
Sub TEST3()
Dim Brr, Z, i&, c, T$, T1$, T2$, T3$, a, b, d
Dim ColNum As Long
c = Application.Match([Invoice!G5], [Data!1:1], 0)
If IsError(c) Then
ColNum = Worksheets("Data").Cells(1, Columns.Count).End(xlToLeft).Column
Worksheets("Data").Cells(1, ColNum + 1) = Worksheets("Invoice").Cells(5, 7)
End If
c = Application.Match([Invoice!G5], [Data!1:1], 0)
Set Z = CreateObject("Scripting.Dictionary")
Brr = Range([Invoice!F10], [Invoice!C65536].End(3))
For i = 1 To UBound(Brr)
T1 = Trim(Brr(i, 1)): T2 = Val(Brr(i, 2)): T3 = Val(Brr(i, 3)): T = T1 & "/" & T2 & "/" & T3
If T1 = "" Then GoTo i01
Z(T) = Val(Brr(i, 4))
i01: Next
Brr = [Data!A1].CurrentRegion
For i = 2 To UBound(Brr)
T1 = Trim(Brr(i, 1)): T2 = Val(Brr(i, 2)): T3 = Val(Brr(i, 3)): T = T1 & "/" & T2 & "/" & T3
If T1 = "" Or Z(T) = "" Then Brr(i - 1, 1) = "": GoTo i02
Brr(i - 1, 1) = Z(T)
i02: Next
[Data!A1].Item(2, c).Resize(UBound(Brr) - 1) = Brr
a = Worksheets("Data").Range("A1").End(xlDown).Row
d = Worksheets("Data").Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To a
Worksheets("Data").Cells(i, 5) = Worksheets("Data").Cells(i, 4) - Application.WorksheetFunction.Sum(Worksheets("Data").Range(Cells(i, 6), Cells(i, d + 1)))
Next i
End Sub |
|