Using Find statement in VBA module causes error 91
 

Have written the following VBA code which appears fine to me:

Application.FindFormat.Clear
ThisWorkbook.Worksheets(reference_sheet).Cells.Fin d(What:=searched_for_instrument_value, After:= _
ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart,
SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

(where reference_sheet and searched_for_instrument_value are parameters
passed to the procedure in which this code resides)
However, the following error is generated every time I run this code:
"Object variable or With block variable not set (Error 91)"
Both parameters passed down to the procedure have valid values in them.

Have tried recording myself using the Find function but the code it produces
is almost identical (except for the variables being used to refer to the
worksheet to llok in & what to look for). Can anybody help me, or point me
in the right direction (e.g. are there common causes for this error other
than those listed in the Help)?

Thanks.

Using Find statement in VBA module causes error 91
 

Dim rng as Range
Application.FindFormat.Clear
set rng = ThisWorkbook.Worksheets(reference_sheet).Cells.Fin d( _
What:=searched_for_instrument_value, _
After:= ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart,
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, )
SearchFormat:=False)

If not rng is nothing then
rng.Select
else
msgbox searched_for_instrument_value & " was not found"
End if

--
Regards,
Tom Ogilvy


"Gsp" wrote in message
...
Have written the following VBA code which appears fine to me:

Application.FindFormat.Clear

ThisWorkbook.Worksheets(reference_sheet).Cells.Fin d(What:=searched_for_instr
ument_value, After:= _
ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart,
SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

(where reference_sheet and searched_for_instrument_value are parameters
passed to the procedure in which this code resides)
However, the following error is generated every time I run this code:
"Object variable or With block variable not set (Error 91)"
Both parameters passed down to the procedure have valid values in them.

Have tried recording myself using the Find function but the code it
produces
is almost identical (except for the variables being used to refer to the
worksheet to llok in & what to look for). Can anybody help me, or point
me
in the right direction (e.g. are there common causes for this error other
than those listed in the Help)?

Thanks.