CODEWITHSUNDEEP
excelvbaruntime-error
leetontea
leetontea

Reputation: 1

Runtime Error 9 on Loop

I have three workbooks; all with information on the same policies, but come from different documents. I'm trying to copy the value of the same cell from each worksheet that has the same worksheet name in workbooks 1 & workbook 3. This is the code that I have:

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

I don't understand how I'm using the subscript wrong. This is my first time coding VBA (first time in 5+ years), so I'm unfamiliar with coding errors.

Thank you!

Upvotes: 0

Views: 176

Answers (2)

user3598756
user3598756

Reputation: 29421

may be you're after this:

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

Upvotes: 0

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

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

Variable i is declared, but used before it's assigned - its value is therefore an implicit 0.

With VBA collections being 1-based, that makes wkb2.Worksheets(i) be out of bounds.

Dim i As Integer
i = 1

'...

shtName = wkb2.Worksheets(i).Name

Will fix it.

You probably want to move it inside the loop though.

Upvotes: 3

Related Questions