You're invoking Range.Find
. That method returns a Range
object reference - and when it does not find what it's told to look for, it returns Nothing
, i.e. a null reference.
TickerRow = HBWS.Cells.Find(What:=TickerString).Row 'Throws an error
What this code is doing (and the working instruction just above it), is assuming that Find
returns a valid object reference.
Apparently HBWS.Cells
does not contain "Total " & TTB
(whatever TTB
is), so your code is effectively trying to invoke Range.Row
against a Range
reference that's Nothing
... which is illegal, and raises run-time error 91 as you're experiencing.
You shouldn't assume that Find
will return a valid reference, ever. Split it up, and validate the returned object reference with an If ... Is Nothing
check:
Set tickerResult = HBWS.Cells.Find(What:=TickerString)
If Not tickerResult Is Nothing Then
tickerRow = tickerResult.Row
Else
'tickerString was not found.
'watch for leading/trailing/extra spaces if the string does *look* legit.
End If
When calling Range.Find
, you should always provide a value for the optional parameters, because its implementation "remembers" values from previous invocations, and this is easily bug-prone.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…