Help with VBA Find()

[vbarookie]

I kept getting the following error message when I ran my macro to find a
string in a worksheet range:

Run-time error '9': Subscript out of range

The debugger point the error to the following line of code:

Set foundPilot = Worksheets("Pilot
Costs").Range("A1:B100").Find(What:="Pilot Total", LookIn:=x1Values)

Tried different strings to find and changed the range but kept getting
the same error. Error msg seems to indicate I have an array
subscripting problem but I am not using arrays in the code. Help!

 

[broro183]

Hi Rookie,
I'm still a bit of a rookie myself, but the normal reason for m
receiving the "Run-time error '9': Subscript out of range" error i
when the range I am referencing doesn't exist.

Possible reasons I can see for your problem are:
*use of "set" requires a "foundpilot" object to exist
*a spelling mistake in your sheet name?
*The fact that your code below uses a "1" instead of an "L" in th
section "LookIn:=x1Values"

Personally, (I don't know what your other uses for the "foundpilot
are, but) I would use something like:
Dim foundpilot As String
foundpilot = Worksheets("Pilot Costs").Range("A1:B100").Find _
(What:="Pilot Total", LookIn:=xlValues).Address

If you need to know the row # or column # that the "Pilot Total" is i
try the below:

Dim PilotTotalRow As Long
Dim PilotTotalCol As Long
PilotTotalRow = Worksheets("Pilot Costs").Range("A1:B100").Find _
(What:="Pilot Total", LookIn:=xlValues).Row
PilotTotalCol = Worksheets("Pilot Costs").Range("A1:B100").Find _
(What:="Pilot Total", LookIn:=xlValues).Column

btw, you may have to change the line wrap as it may be in the wron
place for you.

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience..

 

[vbarookie]

Hi Broro183

Thanks for pointing out that the error lies in 'x1values' which shoul
be 'xlvalues'. The error msg no longer appears.

Regard

 

[broro183]

Beauty!

Thanks for the feedback, pleased I could help.

Rob Brockett
NZ
Always learning & the best way to learn is to experience..