¦p¦ó¥i¥HÅý¤£¬O"JPM"¤£Åã¥Ü¥X¨Ó¡A¤]¤£·|¯d¤@¦æªÅ®æ¡H
- ©«¤l
- 438
- ¥DÃD
- 67
- ºëµØ
- 0
- ¿n¤À
- 531
- ÂI¦W
- 0
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-10-30
- ³Ì«áµn¿ý
- 2024-11-19
|
¦^´_ 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
- 4901
- ¥DÃD
- 44
- ºëµØ
- 24
- ¿n¤À
- 4916
- ÂI¦W
- 44
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Office 20xx
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¥_
- µù¥U®É¶¡
- 2010-4-30
- ³Ì«áµn¿ý
- 2025-2-20
               
|
¦^´_ 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
- 438
- ¥DÃD
- 67
- ºëµØ
- 0
- ¿n¤À
- 531
- ÂI¦W
- 0
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-10-30
- ³Ì«áµn¿ý
- 2024-11-19
|
|
|
|
|
|
|
- ©«¤l
- 438
- ¥DÃD
- 67
- ºëµØ
- 0
- ¿n¤À
- 531
- ÂI¦W
- 0
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-10-30
- ³Ì«áµn¿ý
- 2024-11-19
|
¦^´_ 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
- 438
- ¥DÃD
- 67
- ºëµØ
- 0
- ¿n¤À
- 531
- ÂI¦W
- 0
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-10-30
- ³Ì«áµn¿ý
- 2024-11-19
|
¦^´_ 8# Hsieh
result.rar (486.07 KB)
ÁÙ¬O¨S¦³Åã¥Ü¥X¨Ó. JPM WORKSHEETªÅ¥Õ¤@¤ù~
¥t¥~·Q°Ý
Sub sample()
Dim LastRec As Integer
Dim j As Integer
Dim i As Integer
Dim l As Integer
Dim data() As Range
l = 1
Worksheets("Sheet1").Range("A1").Select
ActiveCell.End(xlDown).Select
LastRec = ActiveCell.Row
For j = 1 To LastRec
i = Application.Match(Sheet1.Cells(1, j), "C:\user\destop\[outstanding payment]Sheet2'!.Range("A:A"), 0) §Ú·QŪ¨ú®à±¥t¥~¤@Óexcelªº¸ê®Æ¡A³o¥y¬O¤£¬O¦³°ÝÃD¡H
If Sheet1.Cells(i, 2).Value <> Sheet2.Cells(i, 2).Value Then
Sheet1.Cells(i, 2).Value = Sheet2.Cells(i, 2).Value
Sheet1.Cells(i, 2).Interior.Color = RGB(255, 200, 255)
End If
Next j
End Sub |
|
|
|
|
|
|
- ©«¤l
- 438
- ¥DÃD
- 67
- ºëµØ
- 0
- ¿n¤À
- 531
- ÂI¦W
- 0
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-10-30
- ³Ì«áµn¿ý
- 2024-11-19
|
¦^´_ 5# c_c_lai
³oÓµ{¦¡¨S¦³¤Ï¬M¡A¬O§_ÁÙ¤í¯Ê¤@¨Ç¸ê®Æ¡H |
|
|
|
|
|
|
- ©«¤l
- 438
- ¥DÃD
- 67
- ºëµØ
- 0
- ¿n¤À
- 531
- ÂI¦W
- 0
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-10-30
- ³Ì«áµn¿ý
- 2024-11-19
|
¦^´_ 4# Hsieh
04.Set Rng = .Range("B:B").SpecialCells(xlCellTypeFormulas, 16) ¥X²{error, ±N§ÚDATA BASEªº JPMÂন1/0,¦ý¦bjpm worksheet¤º´N¨S¦³copy ¸ê®Æ¹L¥h¡I
¥t¥~JPM worksheetªº¸ê®Æ¬O¶¶§Ç±NDATA BASEªº JPM¸ê®Æ§Û¹L¨Ó¡A¦ýÂ\¦ì¬O¤£¤@¼Ë
DATA BASE (SHEET1)
Äæ¼Æ¥ÑA :Z
ITEM NO / STATE / SO / BUYER / AGENT / ETA /RECEIVE DATE/ RECEIVE TRACKING NO/ RECEIVE OBL/RECEIVE OHC/RECEIVE OTHER DOCS/RECEIVE REMARK/SEND DATE/SEND TRACKING NO/ SEND OBL/SEND OHC/SEND OTHER DOCS/SEND REMARK/ JPM DATE/JPM REF NO/JPM OBL/JPM OHC/ JPM OTHER DOCS/JPM REMARK/JPM US ARRANGE PAYMENT ON/JPM HK PICK UP ON
JPM WORKSHEET (JPM)¥uÅã¥Ü³o¨Ç¸ê®Æ
Äæ¼Æ¥ÑA : G
JPM DATE / JPM REF NO / SO/JPM DOCS LIST(JPM OBL, JPM OHC, JPM OTHER DOCS ¦X¨Ö¡^/JPM REMARK/JPM US ARRANGE PAYMENT ON/JPM HK PICK UP ON |
|
|
|
|
|
|
- ©«¤l
- 2035
- ¥DÃD
- 24
- ºëµØ
- 0
- ¿n¤À
- 2031
- ÂI¦W
- 0
- §@·~¨t²Î
- Win7
- ³nÅ骩¥»
- Office2010
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-3-22
- ³Ì«áµn¿ý
- 2024-2-1
|
¦^´_ 1# 198188
¤£ª¾¹D³o¬O§_¬O§Aªº»Ý¨D¡H- Sub Test()
- Sheets("JPM").Visible = False
- End Sub
- Sub Test2()
- Sheets("JPM").Visible = True
- End Sub
½Æ»s¥N½X |
|
|
|
|
|
|