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

¦p¦ó¥i¥HÅý¤£¬O"JPM"¤£Åã¥Ü¥X¨Ó¡A¤]¤£·|¯d¤@¦æªÅ®æ¡H

¦^´_ 10# Hsieh

µ²ªG.rar (198.35 KB)

¥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

TOP

¦^´_ 10# Hsieh


    ¥¼©R¦W.rar (118.82 KB)
ÁÙ¬O¦³¿ù»~

TOP

¦^´_ 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¿ù
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

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

TOP

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

TOP

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

TOP

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

TOP

¦^´_ 15# Hsieh


Outstanding Payments.rar (680.06 KB) HK ETA update.rar (360 KB)

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

TOP

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

TOP

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¨C¤ÑµL©Ò¨Æ¨Æ¡A¬O¤H¥Íªº®ø¶OªÌ¡A¿n·¥¡B¦³¥Î¤~¬O¤H¥Íªº³Ð³yªÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD