VBA: Subscript out of range error

Question

I'm still pretty new with VBA, and keep getting the subscript out of range error. I am trying to write code that reads data from one excel tab ("ip") and writes some of that data to another tab ("rtf"). When the ip tab has duplicate values for the variable ipIPCMP then I want to add a new row below the active row in my second tab rtf. The error occurs at the else part of the conditional statement. Thank you.

UPDATE: changed else to elseif and changed the with statement. Still get subscript out of range.

For i = 1 To n   

connectIPCMP = iPCMP(i)

    For j = 1 To m

    ipIPCMP = Sheets("ip").Cells(j + 1, 2)

        If ipIPCMP = connectIPCMP And k = 1 Then

            supplierList = Sheets("ip").Cells(j, 3)

            Sheets("rft").Cells(3 + j, 4) = list
            k = k + 1

         ElseIf ipointIPCMP = connectIPCMP And k > 1 Then

             With Worksheets("rtf")

                .Range(.Cells(3 + i, 1), .Cells(3 + i, 8)).Activate
                .ActiveCell.Offset(1).EntireRow.Insert

             End With

            Sheets("rft").Cells(3 + j, 4) = supplierList

            k = k + 1

        End If
    Next

`Else: ipointIPCMP = connectIPCMP And k > 1` did you mean `elseif`? — findwindow, Dec 29 '15 at 16:52
Change `Else:` to `ElseIf` and add `Then` to the end of the line. — , Dec 29 '15 at 16:55
You just need `ElseIf`. Using only `Else` means that when the `If` statement isn't next, no matter what, run what's after `Else`. Using `ElseIf` will: When the `If` statement isn't met, look to the condition in `ElseIf` to see if that's met..if not, it will continue (in your case, to `End If`). — BruceWayne, Dec 29 '15 at 16:55
What are you trying to do with `addrow = ...` The activate does not work with the `=`. Are you trying to set a range, or is addrow an array? — Scott Craner, Dec 29 '15 at 17:15
I want to select a range (a row) and then add a row below that row. — Jolien .A, Dec 29 '15 at 17:17
The subscript out of range error is because you misspelled the sheet name.....With Worksheets("rtf") should be With Worksheets("rft") after the End With line. — OpiesDad, Dec 29 '15 at 17:32

score 3 · Answer 1 · edited May 23 '17 at 12:15

3

Change Else: to ElseIf and add Then to the end of the line.

Reference the range properly.

With Worksheets("rtf")
     .Range(.Cells(3 + i, 1), .Cells(3 + i, 8)).Activate
     ActiveCell.Offset(1).EntireRow.Insert
End with

Note .Cells and not Cells. See this.

Better Approach

    For i = 1 To n
        connectIPCMP = iPCMP(i)
        With Worksheets("rtf")   '<~~CHECK THIS WORKSHEET NAME!!!!!
            For j = 1 To m
                ipIPCMP = Worksheets("ip").Cells(j + 1, 2)
                If ipIPCMP = connectIPCMP And k = 1 Then
                    supplierList = Sheets("ip").Cells(j, 3)
                    .Cells(3 + j, 4) = List
                    k = k + 1
                ElseIf ipointIPCMP = connectIPCMP And k > 1 Then
                    .Cells(3 + i, 1).Offset(1).EntireRow.Insert  '<~~only need a single cell if you are going to insert an entire row
                    .Cells(3 + j, 4) = supplierList
                    k = k + 1
                End If
            Next j
        End With
    Next i

The code sample you provided only tells half of the story but the above is the best I can offer without seeing the full story.

¹ _{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}

edited May 23 '17 at 12:15

Community

1
1

answered Dec 29 '15 at 16:57

I changed the with statement now to your suggestion but it still gives me the same error there, With Worksheets("rtf") addrow = Range(.Cells(3 + i, 1), .Cells(3 + i, 8)).Activate End With – Jolien .A Dec 29 '15 at 17:09
You cannot use the `=` and `.Activate` like that. `Range` should be `.Range`. Please see [With ... End With statement](https://msdn.microsoft.com/en-us/library/wc500chb.aspx). – Dec 29 '15 at 17:17
Thank you for your help! I adjusted the code accordingly but still get the same exact error. I have troubles understanding what should be included within the with statement. Can I include the activate statement in the with statement? – Jolien .A Dec 29 '15 at 17:26
The short answer is Yes. The long answer is Why would you want to? Relying on ActiveCell, Select, etc is unreliable at best and error-prone at worst. – Dec 29 '15 at 17:28
btw, did you know that you are using `"rft"` in one place and `"rtf"` in another? Perhaps that is generating the confusion. – Dec 29 '15 at 17:32
Good catch! That changed the type of error. Now I get "Activate method of range class failed" error. (I should be able to figure this one out) You're definitely right that relying on this activate-thing is very error-prone. Thanks alot! – Jolien .A Dec 29 '15 at 17:41

VBA: Subscript out of range error

1 Answers1