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

[µo°Ý] ½Ð±Ð§ï¨}¤èªk¡AÁÂÁÂ~

[µo°Ý] ½Ð±Ð§ï¨}¤èªk¡AÁÂÁÂ~

  1. Sub 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. Dim k As Integer
  7. Dim j As Integer
  8.    
  9.     k = Sheets("state").Range("A1").CurrentRegion.Rows.Count
  10.          For j = 2 To k
  11.      If IsError(Application.VLookup(Worksheets("state").Range("A" & j).Value, Sheets("Result").Range("A:B"), 1, False)) Then
  12.              Sheets("State").Cells(j, "B") = Worksheets("Result").Range("S" & j).Value
  13.              Sheets("State").Cells(j, "O") = Worksheets("Result").Range("L" & j).Value
  14.              Sheets("State").Cells(j, "F") = Worksheets("Result").Range("E" & j).Value
  15.              Sheets("State").Cells(j, "K") = Worksheets("Result").Range("U" & j).Value
  16.              Sheets("State").Cells(j, "L") = Worksheets("Result").Range("V" & j).Value
  17.              Sheets("State").Cells(j, "P") = Worksheets("Result").Range("AL" & j).Value
  18.              Sheets("State").Cells(j, "Q") = Worksheets("Result").Range("Q" & j).Value
  19.              Sheets("State").Cells(j, "R") = Worksheets("Result").Range("M" & j).Value
  20.              Sheets("State").Cells(j, "S") = Worksheets("Result").Range("N" & j).Value
  21.              Sheets("State").Cells(j, "T") = Worksheets("Result").Range("X" & j).Value
  22.              Sheets("State").Cells(j, "U") = Worksheets("Result").Range("Z" & j).Value
  23.              Sheets("State").Cells(j, "W") = Worksheets("Result").Range("K" & j).Value
  24.              Sheets("State").Cells(j, "X") = Worksheets("Result").Range("C" & j).Value
  25.             
  26.             End If
  27.        Next

  28. fs = "W:\PIHK\NEW­»´ä¿ì¤½«Ç¥¿¥»¦¬©ñ³æ°O¿ýFROM 01-MAR-2012 to current(updated).xlsx"
  29. Set Wb = Workbooks.Open(fs)
  30. With ThisWorkbook.Worksheets("State")
  31. For Each A In .Range(.[A2], .Range("A1").End(xlDown))
  32.      Set FRng = Wb.Sheets("¦¬³æ°O¿ý").Range("C:C").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
  33.      If Not FRng Is Nothing Then
  34.            A.Offset(, 9) = FRng.Offset(, 4).Value
  35.            If Rng Is Nothing Then Set Rng = A.Offset(, 7) Else Set Rng = Union(Rng, A.Offset(, 7))
  36.         End If
  37.            Set FRng = Nothing
  38. Next
  39. End With
  40. Wb.Close 0

  41.   fs = "W:\PIHK\NEW­»´ä¿ì¤½«Ç¥¿¥»¦¬©ñ³æ°O¿ýFROM 01-MAR-2012 to current(updated).xlsx"
  42. Set Wb = Workbooks.Open(fs)
  43. With ThisWorkbook.Worksheets("State")
  44. For Each A In .Range(.[A2], .Range("A1").End(xlDown))
  45.      Set FRng = Wb.Sheets("©ñ³æ°O¿ý").Range("C:C").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
  46.      If Not FRng Is Nothing Then
  47.            A.Offset(, 21) = FRng.Offset(, 4).Value
  48.            If Rng Is Nothing Then Set Rng = A.Offset(, 21) Else Set Rng = Union(Rng, A.Offset(, 21))
  49.         End If
  50.            Set FRng = Nothing
  51. Next
  52. End With
  53. Wb.Close 0

  54. fs = "W:\Payment Daily Report\Brazil shipment schedule.xlsx"
  55.   Set Wb = Workbooks.Open(fs)
  56. With ThisWorkbook.Worksheets("State")
  57. For Each A In .Range(.[A2], .Range("A1").End(xlDown))
  58.      Set FRng = Wb.Sheets("sheet1").Range("D:D").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
  59.      If Not FRng Is Nothing Then
  60.            A.Offset(, 7) = FRng.Offset(, 10).Value
  61.            If Rng Is Nothing Then Set Rng = A.Offset(, 5) Else Set Rng = Union(Rng, A.Offset(, 5))
  62.         End If
  63.            Set FRng = Nothing
  64. Next
  65. End With
  66. Wb.Close 0

  67. fs = "W:\Payment Daily Report\daily doc.xlsx"

  68. 'fs = ThisWorkbook.Path & "\payment report 2012.xlsx"
  69. Set Wb = Workbooks.Open(fs)
  70. With ThisWorkbook.Worksheets("State")
  71. For Each A In .Range(.[A2], .Range("A1").End(xlDown))
  72.      Set FRng = Wb.Sheets("DAILY").Range("D:D").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
  73.      If Not FRng Is Nothing Then
  74.            A.Offset(, 13) = FRng.Offset(, 3).Value
  75.            If Rng Is Nothing Then Set Rng = A.Offset(, 11) Else Set Rng = Union(Rng, A.Offset(, 11))
  76.         End If
  77.            Set FRng = Nothing
  78. Next
  79. End With
  80. Wb.Close 0

  81.   fs = "W:\Payment Daily Report\JPMHK RECEIVED RECORD.xlsx"
  82. Set Wb = Workbooks.Open(fs)
  83. With ThisWorkbook.Worksheets("State")
  84. For Each A In .Range(.[A2], .Range("A1").End(xlDown))
  85.      Set FRng = Wb.Sheets("NEW TC ITEMS").Range("C:C").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
  86.      If Not FRng Is Nothing Then
  87.            A.Offset(, 8) = FRng.Offset(, 10).Value
  88.            If Rng Is Nothing Then Set Rng = A.Offset(, 6) Else Set Rng = Union(Rng, A.Offset(, 6))
  89.         End If
  90.            Set FRng = Nothing
  91. Next
  92. End With
  93. Wb.Close 0

  94.    fs = "W:\Payment Daily Report\Outstanding Payments.xlsm"
  95. Set Wb = Workbooks.Open(fs)
  96. With ThisWorkbook.Worksheets("State")
  97. For Each A In .Range(.[A2], .Range("A1").End(xlDown))
  98.      Set FRng = Wb.Sheets("outstanding payments").Range("A:A").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
  99.      If Not FRng Is Nothing Then
  100.            A.Offset(, 6) = FRng.Offset(, 4).Value
  101.            If Rng Is Nothing Then Set Rng = A.Offset(, 4) Else Set Rng = Union(Rng, A.Offset(, 6))
  102.         End If
  103.        Set FRng = Nothing
  104. Next
  105. End With
  106. Wb.Close 0

  107.     fs = "W:\Payment Daily Report\payment report.xlsx"
  108. Set Wb = Workbooks.Open(fs)
  109. With ThisWorkbook.Worksheets("State")
  110. For Each A In .Range(.[A2], .Range("A1").End(xlDown))
  111.      Set FRng = Wb.Sheets("New form of payment report").Range("B:B").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
  112.      If Not FRng Is Nothing Then
  113.            A.Offset(, 12) = FRng.Offset(, 7).Value
  114.            If Rng Is Nothing Then Set Rng = A.Offset(, 10) Else Set Rng = Union(Rng, A.Offset(, 10))
  115.         End If
  116.            Set FRng = Nothing
  117. Next
  118. End With
  119. Wb.Close 0


  120. fs = "W:\Payment Daily Report\HK ETA update.xlsx"
  121. Set Wb = Workbooks.Open(fs)
  122. With ThisWorkbook.Worksheets("State")
  123. For Each A In .Range(.[A2], .Range("A1").End(xlDown))
  124.      Set FRng = Wb.Sheets("­»´ä&®ü¨¾³æ").Range("A:A").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
  125.      If Not FRng Is Nothing Then
  126.            A.Offset(, 2) = FRng.Offset(, 11).Value
  127.            If Rng Is Nothing Then Set Rng = A.Offset(, 2) Else Set Rng = Union(Rng, A.Offset(, 2))
  128.         End If
  129.            Set FRng = Nothing
  130. Next
  131. End With
  132. Wb.Close 0

  133.   fs = "W:\Payment Daily Report\Mainland ETA Update.xlsx"
  134. Set Wb = Workbooks.Open(fs)
  135. With ThisWorkbook.Worksheets("State")
  136. For Each A In .Range(.[A2], .Range("A1").End(xlDown))
  137.      Set FRng = Wb.Sheets("MAILAND ETA").Range("A:A").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
  138.      If Not FRng Is Nothing Then
  139.            A.Offset(, 3) = FRng.Offset(, 9).Value
  140.            If Rng Is Nothing Then Set Rng = A.Offset(, 3) Else Set Rng = Union(Rng, A.Offset(, 3))
  141.         End If
  142.            Set FRng = Nothing
  143. Next
  144. End With
  145. Wb.Close 0

  146. End Sub
½Æ»s¥N½X
¥t¥~¤U­±³o¨â­Ó¥i§_§ï¦¨¦pªG¦b"W:\Payment Daily Report\HK ETA update.xlsx"Sheets("­»´ä&®ü¨¾³æ") ¤º§ä¨ìA.Offset(, 2) = FRng.Offset(, 11).Value¡A¦pªG§ä¤£¨ì´N¦b"W:\Payment Daily Report\Mainland ETA Update.xlsx" Sheets("MAILAND ETA")¤º§ä¡A¦pªG§ä¨ì´NA.Offset(, 2) = FRng.Offset(, 9).Value¡A¦pªG¨â­Ó³£§ä¤£¨ì´N±NA.Offset(, 2) ´N¤£ÅÜ¡Aºû«ù­ì¥»ªº¸ê®Æ¡C

fs = "W:\Payment Daily Report\HK ETA update.xlsx"
Set Wb = Workbooks.Open(fs)
With ThisWorkbook.Worksheets("State")
For Each A In .Range(.[A2], .Range("A1").End(xlDown))
     Set FRng = Wb.Sheets("­»´ä&®ü¨¾³æ").Range("A:A").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
     If Not FRng Is Nothing Then
           A.Offset(, 2) = FRng.Offset(, 11).Value
           If Rng Is Nothing Then Set Rng = A.Offset(, 2) Else Set Rng = Union(Rng, A.Offset(, 2))
        End If
           Set FRng = Nothing
Next
End With
Wb.Close 0

  fs = "W:\Payment Daily Report\Mainland ETA Update.xlsx"
Set Wb = Workbooks.Open(fs)
With ThisWorkbook.Worksheets("State")
For Each A In .Range(.[A2], .Range("A1").End(xlDown))
     Set FRng = Wb.Sheets("MAILAND ETA").Range("A:A").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
     If Not FRng Is Nothing Then
           A.Offset(, 3) = FRng.Offset(, 9).Value
           If Rng Is Nothing Then Set Rng = A.Offset(, 3) Else Set Rng = Union(Rng, A.Offset(, 3))
        End If
           Set FRng = Nothing
Next
End With
Wb.Close 0

¦^´_ 1# 198188

    ¦b¤U´£¨Ñ´X­Ó¤p«Øij..
1.
¥i¥H±N Worksheets("Result") ³]¬°©w¼Æ ( ¦p KK= Worksheets("Result"))
³o¼Ëªº¸Ü

  1.              .Cells(j, "B") = KK.Range("S" & j).Value
  2.              .Cells(j, "O") = KK.Range("L" & j).Value
  3.              .Cells(j, "F") = KK.Range("E" & j).Value
  4.              .Cells(j, "K") = KK.Range("U" & j).Value
  5.              .Cells(j, "L") = KK.Range("V" & j).Value
  6.              .Cells(j, "P") = KK.Range("AL" & j).Value
  7.              .Cells(j, "Q") = KK.Range("Q" & j).Value
  8.              .Cells(j, "R") = KK.Range("M" & j).Value
  9.              .Cells(j, "S") = KK.Range("N" & j).Value
  10.              .Cells(j, "T") = KK.Range("X" & j).Value
  11.              .Cells(j, "U") = KK.Range("Z" & j).Value
  12.              .Cells(j, "W") = KK.Range("K" & j).Value
  13.              .Cells(j, "X") = KK.Range("C" & j).Value
½Æ»s¥N½X
2.¶}±Ò¡B½Æ»sÀɮ׳¡¥÷
µy·L¬Ý¤F¤@¤U ¦³§ó°Êªº¦a¤è¶È( fs ¡Bthisworkbook.worksheets("xxx") ¡B A.Offset(,X))
³o¼Ëªº¸Ü¥i¥H ¦Ò¼{¥Î Select Case ... End Select §@´À¥N Ãþ¦ü¥H¤U¤èªk
  1. '¦p¦³9¶µ
  2. for uuw = 1 to 9
  3.         select case uuw
  4.                 case 1
  5.                      fs = "W:\PIHK\NEW­»´ä¿ì¤½«Ç¥¿¥»¦¬©ñ³æ°O¿ýFROM 01-MAR-2012 to current(updated).xlsx"
  6.                      Set Wb = Workbooks.Open(fs)
  7.                      With ThisWorkbook.Worksheets("State")
  8.                             For Each A In .Range(.[A2], .Range("A1").End(xlDown))
  9.                                  Set FRng = Wb.Sheets("¦¬³æ°O¿ý").Range("C:C").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
  10.                                  If Not FRng Is Nothing Then
  11.                                       A.Offset(, 9) = FRng.Offset(, 4).Value
  12.                                       If Rng Is Nothing Then Set Rng = A.Offset(, 7) Else Set Rng = Union(Rng, A.Offset(, 7))
  13.                                       Set FRng = Nothing
  14.                                  end if
  15.                            Next
  16.                       End With


  17.                 case 2
  18.                 case 3
  19.                 case 4
  20.                 case 5
  21.                 case 6
  22.                 case 7
  23.                 case 8
  24.                 case 9       

  25.         end select
  26.                       Wb.Close 0
  27. next uuw
½Æ»s¥N½X

  ¦h°µ¦h·Q¦h¾Ç²ß¡A¤Ö¬Ý¤Ö¿ù¤Ö°g³~

  ¦h°µ=¦h¦h½m²ß¡A¦h¦h½s¼g¡C
  ¦h·Q=·Q·Q¬°¤°»ò¤H®aµ{¦¡­n¨º¼Ë¼g¡A¦pªG´«¦¨¦Û¤v¡A¤S·|«ç¼g¡C
  ¦h¾Ç²ß=¾Ç²ß¤H®aªºµo°Ý¨Ã¸Ñµª¡A¾Ç²ß¤H®aªº¼gªk

  ¤Ö¬Ý=¥u¬Ý¤£°µ¤]ªPµM

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¯Ê¤fªºªM¤l¡A¦pªG´«¤@­Ó¨¤«×¬Ý¥¦¡A¥¦¤´µM¬O¶êªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD