198188

¦^´_ 10# Hsieh

µ²ªG.rar (198.35 KB)

¤U¸ü¦¸¼Æ: 2

2012-11-13 15:21

¥i¥H±NJPM ¸ê®Æ½Æ»s¹L¥h¤F¡AÁÂÁ¡I
¦ý¬O¦³¨S¦³¤èªk§ïÅÜ¥¦­Ìªº¦ì¸m¡A¦]¬°§Ú¤£¬O­n¥þ³¡·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

198188

¦^´_ 10# Hsieh


    ¥¼©R¦W.rar (118.82 KB)

¤U¸ü¦¸¼Æ: 4

2012-11-13 18:18

ÁÙ¬O¦³¿ù»~

Hsieh

¦^´_ 11# 198188

1. Sub nn()
   
2. Dim Ay(), Rng As Range, m$, A As Range, r&, Ar
   
3. With Sheets("Sheet1") '§ï¦¨¥¿½T¤u§@ªí¦WºÙ
   
4. If Application.CountIf(.Range("B:B"), "JPM") > 0 Then '§PÂ_BÄæ¬O§_¦³JPM
   
5. .Range("B:B").Replace "JPM", "=1/0", xlWhole '±NJPM¥H¤½¦¡¨ú¥N
   
6. Set Rng = .Range("B:B").SpecialCells(xlCellTypeFormulas, 16) '±N¤½¦¡¬°¿ù»~­ÈªºÀx¦s®æ³]¬°ÅܼÆ
   
7. Rng.Value = "JPM" '±N¤½¦¡Á٭즨JPM
   
8. For Each A In Rng
   
9. r = A.Row
   
10. m = .Cells(r, "U") & "¡B" & .Cells(r, "V") & "¡B" & .Cells(r, "W")
    
11. 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, _
    
12. .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)
    
13. ReDim Preserve Ay(s)
    
14. Ay(s) = Ar
    
15. s = s + 1
    
16. Next
    
17. Sheets("JPM").UsedRange.Offset(1).Clear '±NJPM¤u§@ªí¤º®e²MªÅ
    
18. If s > 0 Then Sheets("JPM").[A2].Resize(s, UBound(Ar) + 1) = Application.Transpose(Application.Transpose(Ay)) '±N°}¦C¼g¨ìJPM¤u§@ªí
    
19. End If
    
20. End With
    
21. End Sub

½Æ»s¥N½X

Match¨ç¼Æ¦pªG§ä¤£¨ì²Å¦X¸ê®Æ´N·|¥X¿ù

198188

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

Hsieh

¦^´_ 14# 198188
½M¤lºN¶H
fs¼g¤J¦r¦ê¤£¥i¯à¥X¿ù¡A´Nºâ¬O¸ô®|¿ù»~¤]¤£¬O¦b¸Ó¦æ¥X¿ù
½Ð§â°ÝÃD»¡©ú²M·¡¡A§_«h°ÝÃD¤£¦A¦^´_

198188

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

198188

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

   
¥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

198188

¦^´_ 15# Hsieh


Outstanding Payments.rar (680.06 KB)

¤U¸ü¦¸¼Æ: 6

2012-11-13 21:28

HK ETA update.rar (360 KB)

¤U¸ü¦¸¼Æ: 5

2012-11-13 21:28

ÀH¤Wªþ¥ó¡I³Â·Ð½Ð¬Ý¬Ý¡I·P¿E¤£ºÉ~

198188

¥t¥~§Ú·Q¦A°Ý¤@¼Ë¡I
RECEIVE
SEND
¦U¦³¸ê®Æ¡A¦ý¥i§_¨â­ÓSTATE¦Û§ÚÀˬd¡A¨Ò¦p¡G
RECEIVE¦³¥H ...
198188 µoªí©ó 2012-11-13 21:21

    Docs Record (Update11-10).rar (102.48 KB)

¤U¸ü¦¸¼Æ: 4

2012-11-13 21:36

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ºâ?

Hsieh

¦^´_ 18# 198188
´£°Ý®É½Ð´y­z§Aªº»Ý¨D
¥ú±q§Aªºµ{¦¡½X¥h²q§Aªº»Ý¨D·|³y¦¨«Ü¤jªº®t²§
¬Ý¬Ý¬O§_¦X¥G§Aªº»Ý¨D

1. Sub sample()
   
2. Dim FRng As Range
   
3. Dim A As Range, Rng As Range
   
4. fs = "C:\Documents and Settings\USER\®à­±\HK ETA update.xlsx"
   
5. 'fs = ThisWorkbook.Path & "\HK ETA update.xlsx"'¦P¤@¥Ø¿ý®É¨Ï¥Î
   
6. Set wb = Workbooks.Open(fs)
   
7. With ThisWorkbook.Worksheets("2012")
   
8. For Each A In .Range(.[A2], .Range("A1").End(xlDown))
   
9.    Set FRng = wb.Sheets("­»´ä&®ü¨¾³æ").Range("A:A").Find(A, lookat:=xlWhole)
   
10.    If Not FRng Is Nothing Then
    
11.       If FRng.Offset(, 11) <> A.Offset(, 3) Then
    
12.          A.Offset(, 3) = FRng.Offset(, 11).Value 'Åý2012ªºDÄæµ¥©ó­»´ä&®ü¨¾³æªºLÄæ
    
13.          If Rng Is Nothing Then Set Rng = A.Offset(, 3) Else Set Rng = Union(Rng, A.Offset(, 3))
    
14.       End If
    
15.    End If
    
16.    Set FRng = Nothing
    
17. Next
    
18.         If Not Rng Is Nothing Then Rng.Interior.Color = RGB(255, 200, 255)
    
19. End With
    
20. wb.Close 0
    
21. End Sub

½Æ»s¥N½X