- ©«¤l
- 1440
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1464
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-8-22
|
¦^´_ 11# 198188
ÁÂÁ«e½ú¤@°_¾Ç²ß
«á¾Ç½Æ²ß¤è®×¤ß±oµù¸Ñ¦p¤U,½Ð¸Õ¤F¸Ñ«á¹B¥Î
Option Explicit
Sub TEST()
Dim Arr, Brr, Crr, V, Z, i&, T$
'¡ô«Å§iÅܼÆ
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OZÅܼƬO ¦r¨å
Brr = Range([Data!E2], [Data!A65536].End(3))
'¡ô¥OBrrÅܼƬO¤Gºû°}¦C,¥H¤u§@ªí½d³òÀx¦s®æȱa¤J
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é!
T = Trim(Brr(i, 1)) & "/" & Val(Brr(i, 2)) & "/" & Val(Brr(i, 3))
'¡ô¥OTÅܼƬO1Äæ¦r¦ê.2Äæ¼ÆÈ»P3Äæ¼ÆÈ¥H"/"²Å¸¹¦ê±µ¦¨ªº·s¦r¦ê
If Z.EXISTS(T) Then
'¡ô¦pªGZ¦r¨å¸Ì¦³TÅܼƳokey
MsgBox T & "«½Æ": Exit Sub
'¡ô¥Nªí¸ê®Æ¦³«½Æ,¸õ¥X´£¥Üµ¡,«ö½T©w«áµ²§ôµ{¦¡°õ¦æ
Else '§_«h
Z(T) = Val(Brr(i, 5))
'¡ô¥O¥HTÅܼƬ°key,5Äæ¼ÆȬ°item ¯Ç¤JZ¦r¨å¤¤
Z(T & "/n") = 1
'¡ô¥O¥HTÅܼƳs±µ"/n"²Õ¦¨ªº·s¦r¦ê¬°key,1¬°item ¯Ç¤JZ¦r¨å¤¤
End If
Next
Arr = Range([Invoice!H12], [Invoice!C65536].End(3))
'¡ô¥OArrÅܼƬO¤Gºû°}¦C,¥H¤u§@ªí½d³òÀx¦s®æȱa¤J
ReDim Crr(1 To UBound(Arr), 1 To 6)
'¡ô«Å§iCrrÅܼƬO¤GºûªÅ°}¦C,¨Ã«Å§i¨ä½d³ò
For i = 1 To UBound(Arr)
'¡ô³]¶¶°j°é!
If Trim(Arr(i, 1)) = "" Then GoTo i01
'¡ô¦pªG1Äæ°}¦CÈ¥h°£ÀY§ÀªÅ¥Õ¦r¤¸«á ¬OªÅ¦r¤¸,´N¸õ¨ì¼Ð¥Üi01¦ì¸mÄ~Äò°õ¦æ
T = Trim(Arr(i, 1)) & "/" & Val(Arr(i, 2)) & "/" & Val(Arr(i, 3))
Z(T & "/n") = Z(T & "/n") + 1
'¡ô¥OitemȲ֥[1
If Z(T & "/n") > 2 Then MsgBox T & "«½Æ": Exit Sub Else V = Z(T)
'¡ô¥Nªí¸ê®Æ¦³«½Æ,¸õ¥X´£¥Üµ¡,«ö½T©w«áµ²§ôµ{¦¡°õ¦æ,§_«h´N¥OVÅܼƬO¥HTÅܼƬdZ¦r¨å±o¨ìªºitemÈ
If V = "" Then GoTo i01
'¡ô¦pªGVÅܼƬOªÅ¦r¤¸,´N¸õ¨ì¼Ð¥Üi01¦ì¸mÄ~Äò°õ¦æ
Crr(i, 1) = V
Crr(i, 2) = V - Val(Arr(i, 4))
Crr(i, 3) = Application.Round(Val(Arr(i, 2)) * Val(Arr(i, 3)) / 10 ^ 6, 3)
Crr(i, 4) = Crr(i, 3) - Val(Arr(i, 5))
Crr(i, 5) = Application.Round(Crr(i, 3) * Val(Arr(i, 4)), 3)
Crr(i, 6) = Crr(i, 5) - Val(Arr(i, 6))
'¡ô¥O¥HArr°}¦CȨ̻ݨDpºâ«á±a¤J Crr°}¦C¤¤
i01: Next
[Invoice!J12].Resize(UBound(Crr), 6) = Crr
'¡ô¥OCrr°}¦Cȼg¤JÀx¦s®æ¤¤
End Sub |
|