Sub JamieK002()
Application.ScreenUpdating = False
Sheets("All Data").Select
Dim end1 As Long, b1 As Long, a As Long, i As Long, Rng As Range, Rng1 As Range, Wb As Workbook
i = 2
end1 = Range("D65536").End(3).Row
With Worksheets("All Data")
For b1 = 2 To 5
Cells(b1, 2) = Mid(Cells(b1, 4), 1, 2)
Cells(b1, 3) = Mid(Cells(b1, 4), 4, 5)
If Cells(b1, 2).Value = "PD" Then
Set Rng = Cells(b1, 3)
Workbooks.Open Filename:="\\Wws\14.磊晶二部\生產\系統檔\2014系統檔\" & Mid(Cells(b1, 4), 5, 1) & "月\" & Cells(b1, 2) & "系統檔-" & Mid(Cells(b1, 4), 5, 1) & "月" & ".XLS"
With Worksheets("system")
Set Rng1 = .[A:A].Find(Rng, , , xlWhole)
If Not Rng1 Is Nothing Then
Rng1.Offset(0, 13).Value = Worksheets("All Data").Cells(b1, "A")
End If
End With
MsgBox (Rng1)
Workbooks(i).Close (False)
End If
Next
End With
End Sub
那位高手可以幫忙指導 紅色字那邊出問題作者: luhpro 時間: 2014-9-5 05:27
Sub JamieK002()
Application.ScreenUpdating = False
Sheets("All Data").Select
Dim end1 As Long, b1 ...
JeNXiU 發表於 2014-9-4 22:42
錯誤代碼'9 的錯誤說明是 "陣列索引超出範圍"
一般會發生這種情形是因為它找不到物件,
對照你發生此錯誤的指令之前的指令:
Workbooks.Open Filename:="\\Wws\14.磊晶二部\生產\系統檔\2014系統檔\" & Mid(Cells(b1, 4), 5, 1) & "月\" & Cells(b1, 2) & "系統檔-" & Mid(Cells(b1, 4), 5, 1) & "月" & ".XLS"
With Worksheets("system")
Set Rng1 = .[A:A].Find(Rng, , , xlWhole)
If Not Rng1 Is Nothing Then
第 1 行 開啟了 某個 Excel 檔案 (假設為 A 檔案)
第 2 行 索引 Worksheets("system") (因開啟檔案時會自動 Active 該檔案, 所以 Worksheets("system") 在 A 檔案內)
第 3 行 在 system 工作表找出 Rng1 儲存格
第 4 行 如果有找到則繼續執行
第 5 行 Rng1.Offset(0, 13).Value = Worksheets("All Data").Cells(b1, "A") 就是發生錯誤的指令
研判應該是在 A 檔案內沒有 All Data 工作表造成,
或許你的程式應該改成 :
...
Set Wb=ThisWorkBook ' 加此行
i = 2
...
Rng1.Offset(0, 13).Value = Wb.Worksheets("All Data").Cells(b1, "A")
...