- ©«¤l
- 1446
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1470
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-10-21
|
¥»©«³Ì«á¥Ñ Andy2483 ©ó 2024-2-29 10:55 ½s¿è
ÁÂÁ½׾Â,ÁÂÁ 㴣³¡ªL«e½ú«ü¾É,ÁÂÁ«e½úµo¸ÜÃD¤@°_¾Ç²ß
«Øij«e½ú¦b±o¨ì¨ó§U¥N½X«á¸ÕµÛ¦Û¤v³v¦C¤F¸Ñ¨ä·N¸q,¥²n®É¦Û¤vµù¸Ñ,¤£¤F¸Ñªº³¡¤À¬d½×¾Â,©Î¥¦ºô,©Î°Ý¥N½X²Ó¸`
¥H¤U¬O ã´£³¡ªL«e½úªº¤è®×
Sub Test_A1()
Dim Arr, Brr, xD, xZ As Range, xF As Range, T$, R&, C&, i&
T = [Invoice!G5] '³æ¸¹
If Not T Like "INV########" Then Exit Sub '³æ¸¹¤£²Å¦XINV+8¦ì¤é´Á..¸õ¥X
Set xZ = [Data!a1].Cells(1, Columns.Count).End(1) '§ädata²Ä¤@¦æ³Ì«á«DªÅ
Set xF = [Data!1:1].Find(T, Lookat:=xlWhole) '§ä³æ¸¹¦bdataªºÄæ¦ì
If xF Is Nothing Then Set xZ = xZ(1, 2): Set xF = xZ 'Y³æ¸¹¤£¦s¦b, ¼W¥[¤@Äæ
Set xD = CreateObject("Scripting.Dictionary")
'-------------------------------
Arr = Range([Data!c1], [Data!a1].Cells(Rows.Count, 1).End(3))
Arr(1, 1) = T '±NArr²Ä¤@Ä溮æ©ñ¤J"³æ¸¹"
For i = 2 To UBound(Arr)
T = Arr(i, 1) & "\" & Arr(i, 2) & "\" & Arr(i, 3)
xD(T) = i '¦r¨å°O¾Ð¦æ¦ì¸m
Arr(i, 1) = 0 '±NArr²Ä¤@Äæ©ñ¤J0, ¥H³Æ¶ñ¤J¼Æ¶q
Next i
'----------------------------
Brr = Range([Invoice!h1], [Invoice!a1].Cells(Rows.Count, 1).End(3))
For i = 2 To UBound(Brr)
R = xD(Brr(i, 3) & "\" & Brr(i, 4) & "\" & Brr(i, 5))
If R > 0 Then Arr(R, 1) = Arr(R, 1) + Brr(i, 6)
Next i
'----------------------------
xF.Resize(UBound(Arr)).Value = Arr
With Range([Data!F1], xZ).Resize(UBound(Arr)) '³æ¸¹Äæ®æ¦¡
.ColumnWidth = 15 '²Î¤@Äæ¼e
.Borders.LineStyle = 1 '¥[®Ø
.HorizontalAlignment = xlCenter 'Áa¸m¤¤
.VerticalAlignment = xlCenter '¾î¸m¤¤
End With
[Data!e2].Resize(UBound(Arr) - 1) = "=D2-SUM(F2:" & xZ(2).Address(0, 0) & ")" 'EÄæ"µ²¾l"¤½¦¡(ÀHÄæ¼ÆÅܤÆ)..§R¥hÄæ¤]¥i¥¿½Tpºâ
End Sub |
|