0

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

According to the official documentation, there's only 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

Also, if s.o. can give me a hint how to avoid Offset method in combination with Find function, feel free to shoot. :)

Was looking through forums and the official documentation to rule out causes. Checked assignments and spellings a 1000 times but cannot get behind what's going wrong...

Also, I excluded the Offset method, to see if this results in a different error.

Warcupine
  • 4,460
  • 3
  • 15
  • 24
Marvster
  • 11
  • 2

1 Answers1

0

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
Warcupine
  • 4,460
  • 3
  • 15
  • 24
  • Hi Warcupine, thanks for the help here! Chewing on my hat right now, because of the "xl" which was the error... Been only using VBA since November 22, so I'm quite the beginner. However, all my code works so far without the putting "." in front of the Cells references - can you give me further insights into why you recommend using (e.g.) ".Cells"? (also the "With" is working fine without the ".", but I am activating the "Partner" sheet before ("partner.activate") – Marvster Jan 21 '23 at 14:36
  • It will sooner or later throw an error if `Partner` isn't active, the `Partner.Activate` is unnecessary if you qualify the range. The `with` might look like it is working but it isn't actually being used, you need the `.` to reference it, it is just looking through all cells in the sheet as is. – Warcupine Jan 23 '23 at 13:22