- Sub Detail()
- Dim FRng As Range
- Dim A As Range, Rng As Range
- Dim i As Integer
- Dim LastRec As Integer
- Dim k As Integer
- Dim j As Integer
-
- k = Sheets("state").Range("A1").CurrentRegion.Rows.Count
- For j = 2 To k
- If IsError(Application.VLookup(Worksheets("state").Range("A" & j).Value, Sheets("Result").Range("A:B"), 1, False)) Then
- Sheets("State").Cells(j, "B") = Worksheets("Result").Range("S" & j).Value
- Sheets("State").Cells(j, "O") = Worksheets("Result").Range("L" & j).Value
- Sheets("State").Cells(j, "F") = Worksheets("Result").Range("E" & j).Value
- Sheets("State").Cells(j, "K") = Worksheets("Result").Range("U" & j).Value
- Sheets("State").Cells(j, "L") = Worksheets("Result").Range("V" & j).Value
- Sheets("State").Cells(j, "P") = Worksheets("Result").Range("AL" & j).Value
- Sheets("State").Cells(j, "Q") = Worksheets("Result").Range("Q" & j).Value
- Sheets("State").Cells(j, "R") = Worksheets("Result").Range("M" & j).Value
- Sheets("State").Cells(j, "S") = Worksheets("Result").Range("N" & j).Value
- Sheets("State").Cells(j, "T") = Worksheets("Result").Range("X" & j).Value
- Sheets("State").Cells(j, "U") = Worksheets("Result").Range("Z" & j).Value
- Sheets("State").Cells(j, "W") = Worksheets("Result").Range("K" & j).Value
- Sheets("State").Cells(j, "X") = Worksheets("Result").Range("C" & j).Value
-
- End If
- Next
-
- fs = "W:\PIHK\NEW»´ä¿ì¤½«Ç¥¿¥»¦¬©ñ³æ°O¿ýFROM 01-MAR-2012 to current(updated).xlsx"
- Set Wb = Workbooks.Open(fs)
- With ThisWorkbook.Worksheets("State")
- For Each A In .Range(.[A2], .Range("A1").End(xlDown))
- Set FRng = Wb.Sheets("¦¬³æ°O¿ý").Range("C:C").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
- If Not FRng Is Nothing Then
- A.Offset(, 9) = FRng.Offset(, 4).Value
- If Rng Is Nothing Then Set Rng = A.Offset(, 7) Else Set Rng = Union(Rng, A.Offset(, 7))
- End If
- Set FRng = Nothing
- Next
- End With
- Wb.Close 0
-
- fs = "W:\PIHK\NEW»´ä¿ì¤½«Ç¥¿¥»¦¬©ñ³æ°O¿ýFROM 01-MAR-2012 to current(updated).xlsx"
- Set Wb = Workbooks.Open(fs)
- With ThisWorkbook.Worksheets("State")
- For Each A In .Range(.[A2], .Range("A1").End(xlDown))
- Set FRng = Wb.Sheets("©ñ³æ°O¿ý").Range("C:C").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
- If Not FRng Is Nothing Then
- A.Offset(, 21) = FRng.Offset(, 4).Value
- If Rng Is Nothing Then Set Rng = A.Offset(, 21) Else Set Rng = Union(Rng, A.Offset(, 21))
- End If
- Set FRng = Nothing
- Next
- End With
- Wb.Close 0
-
- fs = "W:\Payment Daily Report\Brazil shipment schedule.xlsx"
- Set Wb = Workbooks.Open(fs)
- With ThisWorkbook.Worksheets("State")
- For Each A In .Range(.[A2], .Range("A1").End(xlDown))
- Set FRng = Wb.Sheets("sheet1").Range("D:D").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
- If Not FRng Is Nothing Then
- A.Offset(, 7) = FRng.Offset(, 10).Value
- If Rng Is Nothing Then Set Rng = A.Offset(, 5) Else Set Rng = Union(Rng, A.Offset(, 5))
- End If
- Set FRng = Nothing
- Next
- End With
- Wb.Close 0
-
- fs = "W:\Payment Daily Report\daily doc.xlsx"
-
- 'fs = ThisWorkbook.Path & "\payment report 2012.xlsx"
- Set Wb = Workbooks.Open(fs)
- With ThisWorkbook.Worksheets("State")
- For Each A In .Range(.[A2], .Range("A1").End(xlDown))
- Set FRng = Wb.Sheets("DAILY").Range("D:D").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
- If Not FRng Is Nothing Then
- A.Offset(, 13) = FRng.Offset(, 3).Value
- If Rng Is Nothing Then Set Rng = A.Offset(, 11) Else Set Rng = Union(Rng, A.Offset(, 11))
- End If
- Set FRng = Nothing
- Next
- End With
- Wb.Close 0
-
- fs = "W:\Payment Daily Report\JPMHK RECEIVED RECORD.xlsx"
- Set Wb = Workbooks.Open(fs)
- With ThisWorkbook.Worksheets("State")
- For Each A In .Range(.[A2], .Range("A1").End(xlDown))
- Set FRng = Wb.Sheets("NEW TC ITEMS").Range("C:C").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
- If Not FRng Is Nothing Then
- A.Offset(, 8) = FRng.Offset(, 10).Value
- If Rng Is Nothing Then Set Rng = A.Offset(, 6) Else Set Rng = Union(Rng, A.Offset(, 6))
- End If
- Set FRng = Nothing
- Next
- End With
- Wb.Close 0
-
- fs = "W:\Payment Daily Report\Outstanding Payments.xlsm"
- Set Wb = Workbooks.Open(fs)
- With ThisWorkbook.Worksheets("State")
- For Each A In .Range(.[A2], .Range("A1").End(xlDown))
- Set FRng = Wb.Sheets("outstanding payments").Range("A:A").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
- If Not FRng Is Nothing Then
- A.Offset(, 6) = FRng.Offset(, 4).Value
- If Rng Is Nothing Then Set Rng = A.Offset(, 4) Else Set Rng = Union(Rng, A.Offset(, 6))
- End If
- Set FRng = Nothing
- Next
- End With
- Wb.Close 0
-
- fs = "W:\Payment Daily Report\payment report.xlsx"
- Set Wb = Workbooks.Open(fs)
- With ThisWorkbook.Worksheets("State")
- For Each A In .Range(.[A2], .Range("A1").End(xlDown))
- Set FRng = Wb.Sheets("New form of payment report").Range("B:B").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
- If Not FRng Is Nothing Then
- A.Offset(, 12) = FRng.Offset(, 7).Value
- If Rng Is Nothing Then Set Rng = A.Offset(, 10) Else Set Rng = Union(Rng, A.Offset(, 10))
- End If
- Set FRng = Nothing
- Next
- End With
- Wb.Close 0
-
-
- 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
-
- 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 |