198188

Desktop.rar (440.64 KB)

¤U¸ü¦¸¼Æ: 1

2013-4-19 11:32

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

Hsieh

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

198188

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

Hsieh

¦^´_ 23# 198188

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

198188

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. 
    
22.   fs = "W:\Payment Daily Report\Mainland ETA Update.xlsm"
    
23. Set wb = Workbooks.Open(fs)
    
24. With ThisWorkbook.Worksheets("State")
    
25. For Each A In .Range(.[A2], .Range("A1").End(xlDown))
    
26.      Set FRng = wb.Sheets("MAILAND ETA").Range("A:A").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
    
27.       If Not FRng Is Nothing Then
    
28.        If Trim(FRng.Offset(, 11).Value) <> "" Then
    
29.      A.Offset(, 1) = FRng.Offset(, 11).Value
    
30.      
    
31.            If rng Is Nothing Then Set rng = A.Offset(, 1) Else Set rng = Union(rng, A.Offset(, 1))
    
32.         End If
    
33.         End If
    
34.        Set FRng = Nothing
    
35. Next
    
36. End With
    
37. wb.Close 0
    
38. end sub

½Æ»s¥N½X

¦^´_ 24# Hsieh

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

Hsieh

¦^´_ 25# 198188

ThisWorkbook¬O«üµ{¦¡½X©Ò¦bªº¬¡­¶Ã¯

198188

¤U¸ü (120.59 KB)

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¸ü

198188

¦^´_  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¤£¹ï

198188

[/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

black_kuo

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