excel - Using the Find VBA function with Defined String

Question

Hey maybe I'm not seeing something obvious here but how can you use the Find VBA function with a predefined variable. I'm using a concatenation of a string assigned from a user form and just "total " in front of it, yet I can't return the row.

Below is my code

Dim HBWS As Worksheet
Dim TickerString As String
TickerString = "Total " & TTB
Set HBWS = Sheets("Hoenheimm Worksheet")


BorrowColumn = HBWS.Cells.Find(What:="Borrow").Column 'Works just fine
TickerRow = HBWS.Cells.Find(What:=TickerString).Row 'Throws an error

Note that TTB is set to a ticker ex. AAPL, and I can check in my local windows that Tickerstring is in fact = to "Total AAPL"

I would expect the .Row column to give me the row on my worksheet as to where this string is located.

EDIT: the error being thrown is as follows...

"Run-Time error '91':

Object Variable or With block variable not set"

Any throughts, Thanks

Answer 1

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.