Help with VBA Find()
 

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!
:confused:


--
vbarookie
------------------------------------------------------------------------
vbarookie's Profile: http://www.excelforum.com/member.php...o&userid=31789
View this thread: http://www.excelforum.com/showthread...hreadid=524195

Help with VBA Find()
 

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

Possible reasons I can see for your problem a
*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 the
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 in
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 wrong
place for you.

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


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=524195

Help with VBA Find()
 

Hi Broro183

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

Regards


--
vbarookie
------------------------------------------------------------------------
vbarookie's Profile: http://www.excelforum.com/member.php...o&userid=31789
View this thread: http://www.excelforum.com/showthread...hreadid=524195

Help with VBA Find()
 

Beauty!

Thanks for the feedback, pleased I could help.

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


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=524195