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

0 votes

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.

Jan 21, 2023 in Others by Kithuzzz
• 38,000 points
1,691 views

1 answer to this question.