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

198188

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

198188

¦^´_ 2# Andy2483


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

198188

¦^´_ 4# Andy2483


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

198188

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

198188

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

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

198188

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

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

198188

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


­ã¤j¡A§Ú¥´¶}ùØ­±µ{¦¡·Q¾Ç²ß¤Î°µ·L½Õ¡A¦ý¬OùØ­±ª`ÄÀ¬O¶Ã½X¡A¥i§_±Nµ{¦¡¶K¦b¦^´_¤W¡A¤è«K§Ú¾Ç²ß¡AÁÂÁ¡I

198188

¦^´_ 12# Andy2483


­ì¥»¬OKH ªº¼Æ¶qcopy ¨ìData ªºÄæD, ¦pªG·Q±NÄæD §ï爲ÄæE¡A ³o­ÓÀ³¸Ó¦b­þ¥y­×§ï¡H

198188

¦^´_ 13# 198188

§ä¨ì¤èªk¤F

    Sub Test_A1()
Dim Arr, Brr, xD, xZ As Range, xF As Range, T$, R&, C&, i&, A, B

A = Worksheets("Data").Range("A1").End(xlDown).Row
For B = 2 To A
Worksheets("Data").Cells(B, 4) = Worksheets("Data").Cells(B, 5)
Next B

T = [data!E1] 'invoice no
Set xZ = [Data!a1].Cells(1, Columns.Count).End(1)  'Find Data last column
Set xF = [Data!1:1].Find(T, Lookat:=xlWhole) 'Find invoice no from Data all column?
If xF Is Nothing Then Set xZ = xZ(1, 2): Set xF = xZ 'if don't find, add one column


Set xD = CreateObject("Scripting.Dictionary")
'-------------------------------
Arr = Range([Data!c1], [Data!a1].Cells(Rows.Count, 1).End(3))
Arr(1, 1) = T 'put Arr first column on invoice
For i = 2 To UBound(Arr)
    T = Arr(i, 1) & "\" & Arr(i, 2) & "\" & Arr(i, 3)
    xD(T) = i 'record column place
    Arr(i, 1) = 0  'set Arr first column 0,for back up to input?
Next i
'----------------------------
Brr = Range([KH!E1], [KH!a1].Cells(Rows.Count, 1).End(3))
For i = 2 To UBound(Brr)
    R = xD(Brr(i, 2) & "\" & Brr(i, 3) & "\" & Brr(i, 4))
    If R > 0 Then Arr(R, 1) = Arr(R, 1) + Brr(i, 5)
Next i
'----------------------------
xF.Resize(UBound(Arr)).Value = Arr

[Data!F2].Resize(UBound(Arr) - 1) = "=E2-SUM(G2:" & xZ(2).Address(0, 0) & ")" 'Column E BAL.
'RESET AND DELETE OLD RECORD
End Sub

198188

¦^´_ 15# Andy2483


   ¦]爲§Ú­×§ï¤F¤@¨Ç®æ¦¡¡A©Ò¥H»Ý­n¦A·L½Õµ{¦¡¡C
ªþ¥ó¤W§Ú°ò©ó¤@¨Ç¹ê»Ú§ïÅܦӰµ¤F¤@¨Ç§ï°Ê¡Aµ{¦¡¤]·L½Õ¤F¡C