循环运行时错误9

Question

我有三个工作簿;所有信息都包含相同的政策信息，但来自不同的文档。我试图在同一单元格的值从具有在工作簿1 &工作簿3.同一工作表名称为每个工作表复制这是我的代码：

Sub foo() 

    Dim wbk1 As Workbook 
    Dim wbk2 As Workbook 
    Dim wkb3 As Workbook 
    Dim shtName As String 
    Dim i As Integer 

    Set wkb1 = Workbooks.Open("C:\Users\lliao\Documents\Trad Reconciliation.xlsx") 
    Set wkb2 = Workbooks.Open("C:\Users\lliao\Documents\TradReconciliation.xlsx") 
    Set wkb3 = Workbooks.Open("C:\Users\lliao\Documents\Measure Trad Recon LS.xlsx") 

    shtName = wkb2.Worksheets(i).Name 

    For i = 2 To wkb2.Worksheets.Count 
     wkb2.Sheets(shtName).Range("D3").Value = wkb1.Sheets(shtName).Range("D2") 
     wkb2.Sheets(shtName).Range("E3").Value = wkb1.Sheets(shtName).Range("E2") 
     wkb2.Sheets(shtName).Range("F3").Value = wkb1.Sheets(shtName).Range("F2") 
     wkb2.Sheets(shtName).Range("D4").Value = wkb3.Sheets(shtName).Range("D2") 
     wkb2.Sheets(shtName).Range("E4").Value = wkb3.Sheets(shtName).Range("E2") 
     wkb2.Sheets(shtName).Range("F4").Value = wkb3.Sheets(shtName).Range("F2") 
    Next i 

End Sub 

我不明白我怎么使用下标错误。这是我第一次编写VBA（5年以上的第一次），所以我不熟悉编码错误。

谢谢！

Answer 1

Dim i As Integer 

Set wkb1 = Workbooks.Open("C:\Users\lliao\Documents\Trad Reconciliation.xlsx") 
Set wkb2 = Workbooks.Open("C:\Users\lliao\Documents\TradReconciliation.xlsx") 
Set wkb3 = Workbooks.Open("C:\Users\lliao\Documents\Measure Trad Recon LS.xlsx") 

shtName = wkb2.Worksheets(i).Name 

可变i声明，但它的分配之前使用 - 因此它的值是一个隐含的0。

由于VBA集合是基于1的，这使得wkb2.Worksheets(i)超出范围。

Dim i As Integer 
i = 1 

'... 

shtName = wkb2.Worksheets(i).Name 

将解决它。

虽然你可能想要在循环中移动它。

Answer 2

可能是你在这之后：

For i = 2 To wkb2.Worksheets.Count 
    wkb2.Sheets(i).Range("D3:F3").Value = wkb1.Sheets(i).Range("D2:F2") 
    wkb2.Sheets(i).Range("D4:F4").Value = wkb3.Sheets(i).Range("D2:F2") 
Next i