CODEWITHSUNDEEP
vbaexcel
Bull Feathers
Bull Feathers

Reputation: 31

VBA performs the line of code then complains that that line was out of range

I specifically get the subscript out of range error when I try to run this code:

Sub CopyData()
    'Setup
    Dim Filename, Pathname As String
    Dim source As Workbook
    Dim destination As Workbook
    Dim nextName As String

    Pathname = ActiveWorkbook.Path & "\Q1 grades fall  2015-2016-1"
    Filename = Dir(Pathname & "*.xls")

    Set source = Workbooks("<name of my file>")
    Set destination = Workbooks.Open(Pathname & Filename)
    'Done with Setup
    lastPlace = 2

    'Iterate through Source rows
    For sourceRow = 5 To 28
    With source.Worksheets("<name of my worksheet>")
        currName = Cells(sourceRow, 1)
        'Iterate through Source columns to collect info
        'Columns: ENG = 4; MAT = 7; SCI = 10; SOC = 13; WOR = 16
        ENG = Cells(sourceRow, 4).Value
        MAT = Cells(sourceRow, 7).Value
        SCI = Cells(sourceRow, 10).Value
        SOC = Cells(sourceRow, 13).Value
        WOR = Cells(sourceRow, 16).Value

        End With

        With destination.Worksheets("Sheet1")
        nextName = Split(Cells(lastPlace + 1, 1).Value, ",")(0)

        MsgBox nextName

        End With

        'If currName = destination's name at lastPlace + 1 Then
        'Iterate through Destination to match currName and fill info
        For destRow = 76 To 99
            destination.Worksheets("Sheet1").Activate

            lastPlace = destRow
        Next destRow
    Next sourceRow
End Sub

My issue happens here:

nextName = Split(Cells(lastPlace + 1, 1).Value, ",")(0)

MsgBox nextName

or at least, this is where VBA SAYS it happens.

The yellow cursor points to the first line. But here's what I don't get (aside from the idea that the first element in my array could be out of range): I get a message box printing out what that value should be (student's last name). I changed it to look at the second element and it printed out the correct value again (same student's first name). And it is consistent with the format of my excel.

So that's my main question: Where is this error and how do I fix it?

But since I literally started learning about VBA yesterday afternoon, I wouldn't mind any additional tips you have for me from looking at my code.

Thank you in advance!

Edit:

I should mention that I tried putting VBA.Split instead. No dice.

Upvotes: 2

Views: 94

Answers (2)

Bull Feathers
Bull Feathers

Reputation: 31

Okay, so while making an example file, I discovered the problem.

While I was testing my code I entirely forgot about the incomplete segment at the bottom (which you need to scroll down to see, lol). The segment happens to be a loop that increments the index "lastPlace" and sets another sheet to active (though the second part shouldn't make a difference I don't think).

This loop was also nested in my original loop so I was inadvertently jumping my index to 99.

That, of course, explains a) why I was seeing a correct MsgBox and then the error on that same line (I feel silly for not noticing that a loop would make something like that happen) and b) why I was getting the out of range error (the very next line was blank).

In case anyone was wondering why I put it in there to begin with, it was supposed to be on one end of a then statement. But I was silly and forgot it was there, still affecting my code.

Upvotes: 1

user4039065
user4039065

Reputation:

You are using a With ... End With statement but ignoring it's purpose as a reference to the parent worksheet. A period (aka . or full stop) is what passes the parent reference along to the members within the With ... End With.

With destination.Worksheets("Sheet1")
    nextName = Split(.Cells(lastPlace + 1, 1).Value, ",")(0)
    MsgBox nextName
End With

Note .Cells and not Cells.

Upvotes: 4

Related Questions