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

[µo°Ý] Àx¦s®æ¥X²{®æ¦¡¬°¤å¦r©Î¶}ÀY¬°³æ¤Þ¸¹ªº¿ù»~

Desktop.rar (440.64 KB)
  1.   fs = "W:\Payment Daily Report\HK ETA update.xlsm"
  2. Set wb = Workbooks.Open(fs)
  3. With ThisWorkbook.Worksheets("State")
  4. For Each A In .Range(.[A2], .Range("A1").End(xlDown))
  5.      Set FRng = wb.Sheets("HK HAIPONG").Range("A:A").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
  6.      If Not FRng Is Nothing Then
  7.           A.Offset(, 1) = FRng.Offset(, 11).Value
  8.           If rng Is Nothing Then Set rng = A.Offset(, 1) Else Set rng = Union(rng, A.Offset(, 1))
  9.         End If
  10.         Set FRng = Nothing
  11. Next
  12. End With
  13. wb.Close 0
½Æ»s¥N½X
¦^´_ 17# Hsieh
³o­Óµ{¦¡À³¸Ó¬O·|¦Û°Ê§ó·sMaster.xlsxªºBÄæ¸ê®Æ¡A¦ý¬O§Úµo²{¦³¨Ç°µ¤£¤F¡A¨Ò¦p209519 Master.xlsx¬O22/3¡A¦ý¬OHK HAIPONG sheet ¬O21/3¡A ¤£À´±o§ó·s¡A½Ð°Ý­þ¸Ì¦³°ÝÃD¡H

¥t¥~¦pªG¦bHK HAIPONG sheet ¤¤§ä¨ì¦³¡A¦ý¬OOffset(, 11).Value¬OªÅ®æ¡A´N¤£ÅÜ,§Ú§ï¤F³o¥yIf Trim(FRng.Offset(, 11).Value) <> "" then  A.Offset(, 1) = FRng.Offset(, 11).Value¡A¦ý¬O¨S¦³§@¥Î¡A½Ð°Ý¬O§_¦³¿ù¡H

fs = "W:\Payment Daily Report\HK ETA update.xlsm"
Set wb = Workbooks.Open(fs)
With ThisWorkbook.Worksheets("State")
For Each A In .Range(.[A2], .Range("A1").End(xlDown))
     Set FRng = wb.Sheets("HK HAIPONG").Range("A:A").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
     If Not FRng Is Nothing Then
        If Trim(FRng.Offset(, 11).Value) <> "" then  A.Offset(, 1) = FRng.Offset(, 11).Value
          If rng Is Nothing Then Set rng = A.Offset(, 1) Else Set rng = Union(rng, A.Offset(, 1))
        End If
        Set FRng = Nothing
Next
End With
wb.Close 0

TOP

¦^´_ 21# 198188

    Set FRng = wb.Sheets("HK HAIPONG").Range("A:A").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
     If Not FRng Is Nothing Then
        If Trim(FRng.Offset(, 11).Value) <> "" then  
             A.Offset(, 1) = FRng.Offset(, 11).Value
             If rng Is Nothing Then Set rng = A.Offset(, 1) Else Set rng = Union(rng, A.Offset(, 1))
       End If
     End If
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 22# Hsieh


    ³o­Óµ{¦¡À³¸Ó¬O·|¦Û°Ê§ó·sMaster.xlsxªºBÄæ¸ê®Æ¡A¦ý¬O§Úµo²{¦³¨Ç°µ¤£¤F¡A¨Ò¦p209519 Master.xlsx¬O22/3¡A¦ý¬OHK HAIPONG sheet ¬O21/3¡A ¤£À´±o§ó·s¡A½Ð°Ý­þ¸Ì¦³°ÝÃD¡H

TOP

¦^´_ 23# 198188

¬Ý§Aªºµ{¦¡½X¦ü¥G¬OÁÙ¦³¥t¤@Àɮ׬°°õ¦æµ{¦¡ªºÀÉ®×
¦ý¬O¦bµ{¦¡½X¤º®e«o¤£¬O°w¹ïMaster.xlsxÀÉ®×°µ§ó·s
³o¼ËÂ_³¹¨ú¸qªº°ÝÃDµLªk¸Ñµª
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

  1. Sub Load_State_Detail()
  2. Dim FRng As Range
  3. Dim A As Range, rng As Range
  4. Dim I As Integer
  5. Dim LastRec As Integer
  6. fs = "W:\Payment Daily Report\HK ETA update.xlsm"
  7. Set wb = Workbooks.Open(fs)
  8. With ThisWorkbook.Worksheets("State")
  9. For Each A In .Range(.[A2], .Range("A1").End(xlDown))
  10.      Set FRng = wb.Sheets("HK HAIPONG").Range("A:A").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
  11.      If Not FRng Is Nothing Then
  12.      If Trim(FRng.Offset(, 11).Value) <> "" Then
  13.           A.Offset(, 1) = FRng.Offset(, 11).Value
  14.           If rng Is Nothing Then Set rng = A.Offset(, 1) Else Set rng = Union(rng, A.Offset(, 1))
  15.         End If
  16.         End If
  17.         Set FRng = Nothing
  18. Next
  19. End With
  20. wb.Close 0

  21.   fs = "W:\Payment Daily Report\Mainland ETA Update.xlsm"
  22. Set wb = Workbooks.Open(fs)
  23. With ThisWorkbook.Worksheets("State")
  24. For Each A In .Range(.[A2], .Range("A1").End(xlDown))
  25.      Set FRng = wb.Sheets("MAILAND ETA").Range("A:A").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
  26.       If Not FRng Is Nothing Then
  27.        If Trim(FRng.Offset(, 11).Value) <> "" Then
  28.      A.Offset(, 1) = FRng.Offset(, 11).Value
  29.      
  30.            If rng Is Nothing Then Set rng = A.Offset(, 1) Else Set rng = Union(rng, A.Offset(, 1))
  31.         End If
  32.         End If
  33.        Set FRng = Nothing
  34. Next
  35. End With
  36. wb.Close 0
  37. end sub
½Æ»s¥N½X
¦^´_ 24# Hsieh

With ThisWorkbook.Worksheets("State") ³o¥y¬O¤£¬O«ü¥Ü¤FMaster.xlsm¤ºªºState Sheet¶Ü¡H

TOP

¦^´_ 25# 198188

ThisWorkbook¬O«üµ{¦¡½X©Ò¦bªº¬¡­¶Ã¯
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ 198188 ©ó 2013-4-22 11:23 ½s¿è

¥¼©R¦W.png
2013-4-22 11:23
b]¦^´_ 26# Hsieh


    ¬O§r¡A§Úµ{¦¡¬O©ñ¦bMASTER.XLSM¤º¡A¦]¬°§Ú©È©ñµ{¦¡·|¾É­PEXCEL¤Ó¤j¡AµLªk¤W¸ü¡A©Ò¥H¤~¤À¥X¨Ó¤W¸ü

TOP

¦^´_  198188
Hsieh µoªí©ó 2013-4-16 19:32



    ¥t¥~¥i§_À°§Ú¬Ý¬Ý²Ä18¡A19ªº¶K¤å¡H
²Ä18½g¬O°ÝÃD¡A²Ä19½g¬Oµ{¦¡¡C¬°¦ó¸ê®Æ®w¦³ªÅ®æ®É¡A·|Åܦ¨³o¼Ë¡A¦ì¸m¤£¹ï

TOP

[/code]¦^´_  Hsieh


[code]Sub Detail()
Dim FRng As Range
Dim a As Range, Rng As Range
Dim ...
198188 µoªí©ó 2013-4-17 11:04


¥t¥~¥i§_À°§Ú¬Ý¬Ý²Ä18¡A19ªº¶K¤å¡H
²Ä18½g¬O°ÝÃD¡A²Ä19½g¬Oµ{¦¡¡C¬°¦ó¸ê®Æ®w¦³ªÅ®æ®É¡A·|Åܦ¨³o¼Ë¡A¦ì¸m¤£¹ï
½Ð°Ý¦³¤èªk¸Ñ¨M³o­Ó°ÝÃD¶Ü¡H

TOP

§Ú¤]¦n·Qª¾¹D¬°¤°»ò¡A¥i¬O§Ú²{¦bÁÙ¤£¯à¤U¸ü@@

TOP

        ÀR«ä¦Û¦b : ­ì½Ì§O¤H´N¬Oµ½«Ý¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD