- 帖子
- 438
- 主題
- 67
- 精華
- 0
- 積分
- 531
- 點名
- 0
- 作業系統
- win7
- 軟體版本
- office 2010
- 閱讀權限
- 50
- 性別
- 男
- 註冊時間
- 2012-10-30
- 最後登錄
- 2025-7-8
|
25#
發表於 2013-4-22 10:55
| 只看該作者
- Sub Load_State_Detail()
- Dim FRng As Range
- Dim A As Range, rng As Range
- Dim I As Integer
- Dim LastRec As Integer
- 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
- End If
- Set FRng = Nothing
- Next
- End With
- wb.Close 0
-
- fs = "W:\Payment Daily Report\Mainland 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("MAILAND ETA").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
- Set FRng = Nothing
- Next
- End With
- wb.Close 0
- end sub
複製代碼 回復 24# Hsieh
With ThisWorkbook.Worksheets("State") 這句是不是指示了Master.xlsm內的State Sheet嗎? |
|