¦p¦ó¥i¥HÅý¤£¬O"JPM"¤£Åã¥Ü¥X¨Ó¡A¤]¤£·|¯d¤@¦æªÅ®æ¡H
- ©«¤l
- 430
- ¥DÃD
- 65
- ºëµØ
- 0
- ¿n¤À
- 521
- ÂI¦W
- 30
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-10-30
- ³Ì«áµn¿ý
- 2024-8-13
|
¦^´_ 10# Hsieh
µ²ªG.rar (198.35 KB)
¥i¥H±NJPM ¸ê®Æ½Æ»s¹L¥h¤F¡AÁÂÁ¡I
¦ý¬O¦³¨S¦³¤èªk§ïÅÜ¥¦Ìªº¦ì¸m¡A¦]¬°§Ú¤£¬On¥þ³¡·h¹L¥h¡A©M§Ú·Q¥¦Ì¦bA:F¶¶§Ç§Û¹L¥h¡C
DATE TRACKING NO SO NO MM BUYER PLANT BOXES CONTAINER NO ORIGIN ETD ETA DOCS LIST REMARK US ARRANGE PAYMENT ON HK PICK UP ON |
|
|
|
|
|
|
- ©«¤l
- 430
- ¥DÃD
- 65
- ºëµØ
- 0
- ¿n¤À
- 521
- ÂI¦W
- 30
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-10-30
- ³Ì«áµn¿ý
- 2024-8-13
|
|
|
|
|
|
|
- ©«¤l
- 4901
- ¥DÃD
- 44
- ºëµØ
- 24
- ¿n¤À
- 4916
- ÂI¦W
- 197
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Office 20xx
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¥_
- µù¥U®É¶¡
- 2010-4-30
- ³Ì«áµn¿ý
- 2024-9-7
![2023¦~¥þ¶Ô¼ú³¹ 2023¦~¥þ¶Ô¼ú³¹](images/common/2023.png) ![2022ªê¦~¥þ¶Ô¼ú³¹ 2022ªê¦~¥þ¶Ô¼ú³¹](images/common/hu.gif) ![2021¤û¦~¥þ¶Ô¼ú³¹ 2021¤û¦~¥þ¶Ô¼ú³¹](images/common/niu.gif) ![2020¹«¦~¥þ¶Ô¼ú³¹ 2020¹«¦~¥þ¶Ô¼ú³¹](images/common/shu.gif) ![2019 ½Þ¦~¥þ¶Ô¼ú³¹ 2019 ½Þ¦~¥þ¶Ô¼ú³¹](images/common/zhu.gif) ![2018 ª¯¦~¥þ¶Ô¼ú³¹ 2018 ª¯¦~¥þ¶Ô¼ú³¹](images/common/gou.gif) ![2017 Âû¦~¥þ¶Ô¼ú³¹ 2017 Âû¦~¥þ¶Ô¼ú³¹](images/common/ji.gif) ![2016µU¦~¥þ¶Ô¼ú³¹ 2016µU¦~¥þ¶Ô¼ú³¹](images/common/hou.gif) ![2015¦Ï¦~¥þ¶Ô¼ú³¹ 2015¦Ï¦~¥þ¶Ô¼ú³¹](images/common/yang.gif) ![2014°¨¦~¥þ¶Ô¼ú³¹ 2014°¨¦~¥þ¶Ô¼ú³¹](images/common/ma.gif) ![2013³D¦~¥þ¶Ô¼ú³¹ 2013³D¦~¥þ¶Ô¼ú³¹](images/common/she.gif) ![2012Às¦~¥þ¶Ô¼ú³¹ 2012Às¦~¥þ¶Ô¼ú³¹](images/common/long.gif) ![½×¾Â°^Äm¾±³¹ ½×¾Â°^Äm¾±³¹](images/common/medal5.gif) ![¼ö¤ß¤½¯q¾±³¹ ¼ö¤ß¤½¯q¾±³¹](images/common/medal15.gif) ![2011¨ß¦~¥þ¶Ô¼ú³¹ 2011¨ß¦~¥þ¶Ô¼ú³¹](images/common/tu.gif)
|
¦^´_ 11# 198188 - Sub nn()
- Dim Ay(), Rng As Range, m$, A As Range, r&, Ar
- With Sheets("Sheet1") '§ï¦¨¥¿½T¤u§@ªí¦WºÙ
- If Application.CountIf(.Range("B:B"), "JPM") > 0 Then '§PÂ_BÄæ¬O§_¦³JPM
- .Range("B:B").Replace "JPM", "=1/0", xlWhole '±NJPM¥H¤½¦¡¨ú¥N
- Set Rng = .Range("B:B").SpecialCells(xlCellTypeFormulas, 16) '±N¤½¦¡¬°¿ù»~ȪºÀx¦s®æ³]¬°ÅܼÆ
- Rng.Value = "JPM" '±N¤½¦¡ÁÙ즨JPM
- For Each A In Rng
- r = A.Row
- m = .Cells(r, "U") & "¡B" & .Cells(r, "V") & "¡B" & .Cells(r, "W")
- Ar = Array(.Cells(r, "S").Value, .Cells(r, "T").Value, .Cells(r, "C").Value, .Cells(r, "AA").Value, .Cells(r, "D").Value, .Cells(r, "AB").Value, _
- .Cells(r, "AC").Value, .Cells(r, "AD").Value, .Cells(r, "AE").Value, .Cells(r, "AF").Value, .Cells(r, "F").Value, m, .Cells(r, "X").Value, .Cells(r, "Y").Value, .Cells(r, "Z").Value)
- ReDim Preserve Ay(s)
- Ay(s) = Ar
- s = s + 1
- Next
- Sheets("JPM").UsedRange.Offset(1).Clear '±NJPM¤u§@ªí¤º®e²MªÅ
- If s > 0 Then Sheets("JPM").[A2].Resize(s, UBound(Ar) + 1) = Application.Transpose(Application.Transpose(Ay)) '±N°}¦C¼g¨ìJPM¤u§@ªí
- End If
- End With
- End Sub
½Æ»s¥N½X Match¨ç¼Æ¦pªG§ä¤£¨ì²Å¦X¸ê®Æ´N·|¥X¿ù |
|
¾Ç®üµL²P_¤£®¢¤U°Ý
|
|
|
|
|
- ©«¤l
- 430
- ¥DÃD
- 65
- ºëµØ
- 0
- ¿n¤À
- 521
- ÂI¦W
- 30
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-10-30
- ³Ì«áµn¿ý
- 2024-8-13
|
¦^´_ 10# Hsieh
Sub sample()
Dim LastRec As Integer
Dim j As Integer
Dim i As Integer
Dim l As Integer
Dim data() As Range
l = 1
fs = "C:\Documents and Settings\USER\®à±\HK ETA update.xlsx" ³o¥y¥X²{error °õ¦æ¶¥¬q¿ù»~'13'«¬ºA¤£²Å¦X¡I
Set wb = Workbooks.Open(fs)
Workbooks("outstanding payments").Worksheets("2012").Range("A1").Select ³o
ActiveCell.End(xlDown).Select
LastRec = ActiveCell.Row
For j = 1 To LastRec
i = Application.Match(Worksheets("2012").Cells(j, 1), wb.Sheets("»´ä&®ü¨¾³æ").Range("A:A"), 0)
If Worksheets("2012").Cells(i, 4).Value <> wb.Sheets("»´ä&®ü¨¾³æ").Cells(i, 12).Value Then
Worksheets("2012").Cells(i, 4).Value = wb.Sheets("»´ä&®ü¨¾³æ").Cells(i, 2).Value
Worksheets("2012").Cells(i, 4).Interior.Color = RGB(255, 200, 255)
End If
Next j
wb.Close 0
End Sub |
|
|
|
|
|
|
- ©«¤l
- 430
- ¥DÃD
- 65
- ºëµØ
- 0
- ¿n¤À
- 521
- ÂI¦W
- 30
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-10-30
- ³Ì«áµn¿ý
- 2024-8-13
|
¦^´_ 13# Hsieh
¥i¥H¤F¡IÁÂÁ¡I
11/11/2012
12/11/2012
13/11/2012
15/11/2012
15/11/2012
18/11/2012
19/11/2012
11/11/2012
11/11/2012
11/11/2012
11/11/2012
11/12/2012
13/11/2012
15/11/2012
15/11/2012
18/11/2012
19/11/2012
11/11/2012
11/11/2012
11/11/2012
¦ý¬O§Ú·Q°Ý¡A¬°¦ó²Ä¤GÓªº¤é´Á·|Åܦ¨11/12/2012?¬Oþ¸Ì¥X²{°ÝÃD¡H¬O¤£¬O¦]¬°§Ú³]©w¿é¤J®É¦b¤é´Á«e¥['¤~³o¼Ë¡H¦ý¤]¥u¦³¤@Ó¥X¿ù¡A¤£¬O¥þ³¡¥X¿ù¡I
¬O¤£¬O¸ê®Æ®w¤º¨S¦³JPMªº¸ê®Æ´N·|¥X²{°ÝÃD¡H¨º¬O¤£¬O¥i¥H¥[¥y¡A¦pªG¨S¦³JPMªº¸ê®Æ´N¤£°õ¦æ³oÓµ{¦¡,¨º¼Ë´N¨S¨Æ¤F¡H |
|
|
|
|
|
|
- ©«¤l
- 430
- ¥DÃD
- 65
- ºëµØ
- 0
- ¿n¤À
- 521
- ÂI¦W
- 30
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-10-30
- ³Ì«áµn¿ý
- 2024-8-13
|
¦^´_ Hsieh
¥i¥H¤F¡IÁÂÁ¡I
11/11/2012
12/11/2012
13/11/2012
15/11/2012
15/11/2012
18 ...
198188 µoªí©ó 2012-11-13 21:07 ![](https://forum.twbts.com/images/common/back.gif)
¥t¥~§Ú·Q¦A°Ý¤@¼Ë¡I
RECEIVE
SEND
¦U¦³¸ê®Æ¡A¦ý¥i§_¨âÓSTATE¦Û§ÚÀˬd¡A¨Ò¦p¡G
RECEIVE¦³¥H¤U¸ê®Æ
207626 / XIANG / CHEONG / 23-OCT-12 / 15/11/2012 / DHL200 / OBL / / /PM
207626 / XIANG / CHEONG / 23-OCT-12 / 18/11/2012 / DHL201 / / OHC / /AM
207626 / XIANG / CHEONG / 23-OCT-12 / 19/11/2012 / DHL202/ / / CO /AM
SEND ¦³¥H¤U¸ê®Æ
207626 / XIANG / CHEONG / 23-OCT-12 / 19/11/2012 / SF111 / / OHC / /AM
µM«á¹q¸£·|¦Û°Ê°OºâÁÙ¦³þ¨Ç¦b¤â¤WÁÙ¨S±H¥X¡I
207626 / XIANG / CHEONG / 23-OCT-12 / 15/11/2012,19/11/2012 / DHL200,DHL202 / OBL,CO / PM,AM |
|
|
|
|
|
|
- ©«¤l
- 430
- ¥DÃD
- 65
- ºëµØ
- 0
- ¿n¤À
- 521
- ÂI¦W
- 30
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-10-30
- ³Ì«áµn¿ý
- 2024-8-13
|
|
|
|
|
|
|
- ©«¤l
- 430
- ¥DÃD
- 65
- ºëµØ
- 0
- ¿n¤À
- 521
- ÂI¦W
- 30
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-10-30
- ³Ì«áµn¿ý
- 2024-8-13
|
¥t¥~§Ú·Q¦A°Ý¤@¼Ë¡I
RECEIVE
SEND
¦U¦³¸ê®Æ¡A¦ý¥i§_¨âÓSTATE¦Û§ÚÀˬd¡A¨Ò¦p¡G
RECEIVE¦³¥H ...
198188 µoªí©ó 2012-11-13 21:21 ![](https://forum.twbts.com/images/common/back.gif)
Docs Record (Update11-10).rar (102.48 KB)
vba¥i¥H°µ¨ì¤@ÓexcelÅý¦h¦ì¥Î®a¦P®É¨Ï¥Î¡A¿é¤J¡BÀx¦s¡H
¦pªG¤£¯àªº¸Ü¡Avba¥i§_¥Î¥|ÓexcelÅý¥Î®a¨Ï¥Î¡AµM«á¦A±N³o¥|Óexcelªº¸ê®Æ©w®É¶Ç¨ìªþ¥óÁ`ªí,µM«á¦A¹Bºâ? |
|
|
|
|
|
|
- ©«¤l
- 4901
- ¥DÃD
- 44
- ºëµØ
- 24
- ¿n¤À
- 4916
- ÂI¦W
- 197
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Office 20xx
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¥_
- µù¥U®É¶¡
- 2010-4-30
- ³Ì«áµn¿ý
- 2024-9-7
![2023¦~¥þ¶Ô¼ú³¹ 2023¦~¥þ¶Ô¼ú³¹](images/common/2023.png) ![2022ªê¦~¥þ¶Ô¼ú³¹ 2022ªê¦~¥þ¶Ô¼ú³¹](images/common/hu.gif) ![2021¤û¦~¥þ¶Ô¼ú³¹ 2021¤û¦~¥þ¶Ô¼ú³¹](images/common/niu.gif) ![2020¹«¦~¥þ¶Ô¼ú³¹ 2020¹«¦~¥þ¶Ô¼ú³¹](images/common/shu.gif) ![2019 ½Þ¦~¥þ¶Ô¼ú³¹ 2019 ½Þ¦~¥þ¶Ô¼ú³¹](images/common/zhu.gif) ![2018 ª¯¦~¥þ¶Ô¼ú³¹ 2018 ª¯¦~¥þ¶Ô¼ú³¹](images/common/gou.gif) ![2017 Âû¦~¥þ¶Ô¼ú³¹ 2017 Âû¦~¥þ¶Ô¼ú³¹](images/common/ji.gif) ![2016µU¦~¥þ¶Ô¼ú³¹ 2016µU¦~¥þ¶Ô¼ú³¹](images/common/hou.gif) ![2015¦Ï¦~¥þ¶Ô¼ú³¹ 2015¦Ï¦~¥þ¶Ô¼ú³¹](images/common/yang.gif) ![2014°¨¦~¥þ¶Ô¼ú³¹ 2014°¨¦~¥þ¶Ô¼ú³¹](images/common/ma.gif) ![2013³D¦~¥þ¶Ô¼ú³¹ 2013³D¦~¥þ¶Ô¼ú³¹](images/common/she.gif) ![2012Às¦~¥þ¶Ô¼ú³¹ 2012Às¦~¥þ¶Ô¼ú³¹](images/common/long.gif) ![½×¾Â°^Äm¾±³¹ ½×¾Â°^Äm¾±³¹](images/common/medal5.gif) ![¼ö¤ß¤½¯q¾±³¹ ¼ö¤ß¤½¯q¾±³¹](images/common/medal15.gif) ![2011¨ß¦~¥þ¶Ô¼ú³¹ 2011¨ß¦~¥þ¶Ô¼ú³¹](images/common/tu.gif)
|
¦^´_ 18# 198188
´£°Ý®É½Ð´yz§Aªº»Ý¨D
¥ú±q§Aªºµ{¦¡½X¥h²q§Aªº»Ý¨D·|³y¦¨«Ü¤jªº®t²§
¬Ý¬Ý¬O§_¦X¥G§Aªº»Ý¨D- Sub sample()
- Dim FRng As Range
- Dim A As Range, Rng As Range
- fs = "C:\Documents and Settings\USER\®à±\HK ETA update.xlsx"
- 'fs = ThisWorkbook.Path & "\HK ETA update.xlsx"'¦P¤@¥Ø¿ý®É¨Ï¥Î
- Set wb = Workbooks.Open(fs)
- With ThisWorkbook.Worksheets("2012")
- For Each A In .Range(.[A2], .Range("A1").End(xlDown))
- Set FRng = wb.Sheets("»´ä&®ü¨¾³æ").Range("A:A").Find(A, lookat:=xlWhole)
- If Not FRng Is Nothing Then
- If FRng.Offset(, 11) <> A.Offset(, 3) Then
- A.Offset(, 3) = FRng.Offset(, 11).Value 'Åý2012ªºDÄæµ¥©ó»´ä&®ü¨¾³æªºLÄæ
- If Rng Is Nothing Then Set Rng = A.Offset(, 3) Else Set Rng = Union(Rng, A.Offset(, 3))
- End If
- End If
- Set FRng = Nothing
- Next
- If Not Rng Is Nothing Then Rng.Interior.Color = RGB(255, 200, 255)
- End With
- wb.Close 0
- End Sub
½Æ»s¥N½X |
|
¾Ç®üµL²P_¤£®¢¤U°Ý
|
|
|
|
|