ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

´M§ä¦³¨S¦³¬Û¦P¼Æ¾ÚªºÄæ¦ì

´M§ä¦³¨S¦³¬Û¦P¼Æ¾ÚªºÄæ¦ì

Data ªíªº¸ê®Æ¨ÓŪ¨ú Invoice ªíªº¸ê®Æ, ¥ý§ä´MData ªí©Ò¦³Ä榳¨S¦³¸òInvoice ªíÀx¦s®æG5 ¬Û¦Pªº¡C

¦pªG¦³¡A¦b¸ÓÄæ¾Þ§@¤U­±¡G
Dataªí ÄæA ¹ï¤ñ  Invoiceªí ÄæC
Dataªí ÄæB ¹ï¤ñ  Invoiceªí ÄæD
Dataªí ÄæC ¹ï¤ñ  Invoiceªí ÄæE
¤T¼Ë³£¬Û¦P¡AŪ¨ú Invoice ªí ÄæF ªº¼Æ­È¨ìData ªí¸òInvoice G5 ªíÀx¦s®æ¬Û¦Pªº¸ÓÄæ¦ì

¦pªG¨S¦³¡A¦b³Ì«á¤@Äæ¾Þ§@¤U­±¡G
Dataªí ÄæA ¹ï¤ñ  Invoiceªí ÄæC
Dataªí ÄæB ¹ï¤ñ  Invoiceªí ÄæD
Dataªí ÄæC ¹ï¤ñ  Invoiceªí ÄæE
¤T¼Ë³£¬Û¦P¡AŪ¨ú Invoice ªí ÄæF ªº¼Æ­È¨ìData ªí³Ì«á¤@Äæ

DataªíÄæE ­pºâ: DataªíÄæD ´î DataªíÄæF ¶}©l¨ì³Ì«á¦³¼Æ­Èªº¤@Äæ

¦pªGInvoice ªí ÄæC - E²Õ¦X¦bData ªíÄæA - C ¨S¦³§ä¨ìªº¶µ¥Ø¡A¼u¥Xµøµ¡¦C¥X³o¨Ç²Õ¦X

½d¨ÒC.rar (138.72 KB)

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2024-2-27 16:41 ½s¿è

¦^´_ 1# 198188

ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾Ç¤ù¬q¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò

Option Explicit
'¦¹¬q¬O §ä´MData ªí©Ò¦³Ä榳¸òInvoice ªíÀx¦s®æG5 ¬Û¦Pªº,
'¤T¼Ë³£¬Û¦P¡AŪ¨ú Invoice ªí ÄæF ªº¼Æ­È¨ìData ªí¸òInvoice G5 ªíÀx¦s®æ¬Û¦Pªº¸ÓÄæ¦ì

Sub TEST()
Dim Brr, Z, i&, c, T$, T1$, T2$, T3$
c = Application.Match([Invoice!G5], [Data!1:1], 0)
Set Z = CreateObject("Scripting.Dictionary")
If IsError(c) Then MsgBox "§ä¤£¨ìG5ÃöÁä¦r": Exit Sub
Brr = Range([Invoice!F12], [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
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 2# Andy2483


    ¦pªG§ä¤£¨ìG5, À³¸Ó§â©Ò¦³°Ñ¼Æ¾É¤J¨ì³Ì«á¤@¦æ¡A¤£¬OÅã¥Ü¡§§ä¤£¨ìG5ªº°Ñ¼Æ¡¨

TOP

¦^´_ 3# 198188

½Ð«e½ú¦Û¤v¥ý¸Õ¸Õ¬Ý
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 4# Andy2483


    ¯à¤£¯à§iª¾«ç¼Ë§ä³Ì«á¤@Äæ¡A§Ú¥uÀ´±o§ä³Ì«á¤@¦æ¡C
¥t¥~³Ì«á¦³­Ó­pºâ¡AÄæD ´î¥h ÄæF ¨ì³Ì«á¤@¦æ¡A³o¥yÀ³¸Ó¦p¦ó¼g¡H

TOP

¦^´_ 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

TOP

¦³«h¨O´«·s, µL«h·s¼W//

Xl0000200.rar (131.99 KB)

TOP

¦^´_ 7# ­ã´£³¡ªL

¦pªG¦³­«½Æ¡A³o­Ó¦³¨S¦³¦Û°Ê¥[Á`¼Æ¶q¡H

TOP

¦^´_ 8# 198188


invioce ­Y¦³­«ÂÐ, ·|¥[Á`,
ª`·N:dataªí¬O­«·s¶×Á`, ªº·|¥ý²M°£

TOP

¦^´_ 9# ­ã´£³¡ªL

´ú¸Õ¹L¡Aªº½T¥i¥H¡C
½Ð°Ý¥i§_³Ì«á¥[¤@­Ó¥\¯à¡A´£¿ôINVOICE ¦³­þ¨Ç¦bDATA §ä¤£¨ìªº©Ò¦³¶µ¥Ø¡C
¥i¥H¥Î顔¦âhighlight³o¨Ç¶µ¥Ø ©ÎªÌ¼u­Óµøµ¡Åã¥Ü³o¨Çªº©ú²Ó

TOP

        ÀR«ä¦Û¦b : ¤H­nª¾ºÖ¡B±¤ºÖ¡B¦A³yºÖ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD