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,020 points
984 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, 2023 by narikkadan
• 63,680 points

Related Questions In Others

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, 2023 in Others by narikkadan
• 63,680 points
2,692 views
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, 2023 in Others by narikkadan
• 63,680 points
826 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 Jan 29, 2023 in Others by narikkadan
• 63,680 points
920 views
0 votes
1 answer

Excel VBA - Out of memory when create a dynamic array formula

Try this: Sub MyArray() Range("A1").Formula2 = "=R[2]C[2]:R[2]C[6]" End Sub But that ...READ MORE

answered Feb 9, 2023 in Others by narikkadan
• 63,680 points
715 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
• 63,680 points
688 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
778 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, 2023 in Others by narikkadan
• 63,680 points
1,096 views
0 votes
1 answer

Getting data out of a cell with a #NAME? error in Excel VBA

If you need VBA, use .Formula: Dim f As ...READ MORE

answered Feb 3, 2023 in Others by narikkadan
• 63,680 points
634 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