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
– findwindow, Commented Dec 29, 2015 at 16:52
Change Else: to ElseIf and add Then to the end of the line. — user4039065
– user4039065, Commented Dec 29, 2015 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
– BruceWayne, Commented Dec 29, 2015 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
– Scott Craner, Commented Dec 29, 2015 at 17:15
I want to select a range (a row) and then add a row below that row. — Jolien .A
– Jolien .A, Commented Dec 29, 2015 at 17:17

Community · Accepted Answer · 2017-05-23 12:15:50Z

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, 2017 at 12:15

CommunityBot

11 silver badge

answered Dec 29, 2015 at 16:57

user4039065

Sign up to request clarification or add additional context in comments.

6 Comments

Jolien .A Over a year ago

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

user4039065 Over a year ago

You cannot use the = and .Activate like that. Range should be .Range. Please see With ... End With statement.

Jolien .A Over a year ago

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?

user4039065 Over a year ago

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.

user4039065 Over a year ago

btw, did you know that you are using "rft" in one place and "rtf" in another? Perhaps that is generating the confusion.

|

Collectives™ on Stack Overflow

VBA: Subscript out of range error

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related