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 in Others by Kithuzzz
• 27,740 points
33 views

1 answer to this question.

0 votes

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
answered Jan 21 by narikkadan
• 51,240 points

Related Questions In Others

0 votes
1 answer

VBA Range.Offset Error 1004 Range beyond scope of sheet Dynamic Range

Check the .Row first: With Sheets("Line 3").Range("G1024").End(xlUp) ...READ MORE

answered Jan 12 in Others by narikkadan
• 51,240 points
44 views
0 votes
1 answer

Excel to Word Macro resulting in Run-time error 462

Are you attempting this (UNTESTED)? I've commented ...READ MORE

answered 2 days ago in Others by narikkadan
• 51,240 points
33 views
0 votes
1 answer
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 51,240 points
1,071 views
0 votes
1 answer

Multiple find and replace in MS Word from a list in MS Excel

If I understand you correctly, you want ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 51,240 points
83 views
0 votes
1 answer
0 votes
1 answer

Examples for string find in Python

you can use str.index too: >>> 'sdfasdf'.index('cc') Traceback ...READ MORE

answered Aug 29, 2018 in Python by Priyaj
• 58,100 points
354 views
0 votes
1 answer
0 votes
1 answer

VBA code to select only a table. I am getting a Run-time error '1004'; Method 'Range' of object'_Global' failed

No copy/paste, just direct assignment use.Value Sub Final_Report() ...READ MORE

answered Jan 13 in Others by narikkadan
• 51,240 points
64 views
0 votes
1 answer

How to increment the Range of a For Each loop - Excel VBA

Your formula seems to sum 1 single ...READ MORE

answered Jan 7 in Others by narikkadan
• 51,240 points
58 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP