Excel VBA - Subscript Out of Range Error Run Time Error 9

Question

I'm getting a runtime error (9) Subscript out of range when trying to use Cells. Find Function.

According to the official documentation, there are only a few possible causes:

• referenced a nonexistent array element
• declared an array but didn't specify the number of elements
• referenced a nonexistent collection member
• use the ! operator with a collection, the ! implicitly specifies a key

Here's some of the code that seems relevant to me, maybe someone has an idea:

Dim Partner As Worksheet
Dim rangePartner As Range
Set Partner = Worksheets("Partner")
Set rangePartner = Range(Partner.Cells(2, 2), Partner.Cells(2, 2).End(xlDown))
    
Dim partnerid As String  
Dim segment As String
    
With rangePartner
    segment = Cells.Find(partnerid, LookIn:=Values, SearchOrder:=xlByRows).Offset(0, 3)
End With

Please feel free to email me a tip on how to avoid using the Offset approach in conjunction with the Find function as well.

I searched forums and official documentation to check for potential causes. I've checked assignments and spelling a thousand times, but I still don't understand what's wrong.

In order to see if the Offset method causes a different issue, I also excluded it.

Answer 1

Set rangePartner = Range(Partner.Cells(2, 2), Partner.Cells(2, 2).End(xlDown)) Needs an explicit parent object for the Range otherwise it uses the active sheet which will cause problems if that sheet is not Partner, since the cells use Partner.

Your With is currently doing nothing, you need a . infront of cells.

LookIn:=Values should be xlvalues

Don't use a Range method or property in the same line as Find if Find fails then the program will error since it returns an empty object that has no properties or methods.

I don't see a value for partnerid.

All together something like this:

Dim Partner As Worksheet
Dim rangePartner As Range
Set Partner = Worksheets("Partner")

With Partner
    Set rangePartner = .Range(.Cells(2, 2), .Cells(2, 2).End(xlDown))
End With
Dim partnerid As String 'This still needs a value assigned
Dim segment As Range
    
With rangePartner
    Set segment = .Cells.Find(partnerid, LookIn:=xlValues, SearchOrder:=xlByRows)
    If Not segment Is Nothing Then
        Set segment = segment.Offset(0, 3)
    End If
End With