CODEWITHSUNDEEP
vbaexcel
Jolien .A
Jolien .A

Reputation: 173

VBA: Subscript out of range error

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

Next

Upvotes: 1

Views: 475

Answers (1)

user4039065
user4039065

Reputation:

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

  2. 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.

Upvotes: 3

Related Questions