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

Don't even make it that far. I just edited my post to detail the error. — dom176
– dom176, Commented Mar 23, 2018 at 18:18
If .Find fails it returns Nothing so accessing .Row will throw error 91. Test by seeing if range("A1").Value = TickerString is true (replace A1 with the address of the cell with the real corresponding value) — Alex K.
– Alex K., Commented Mar 23, 2018 at 18:28

Mathieu Guindon · Accepted Answer · 2018-03-23 18:28:40Z

4

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.

answered Mar 23, 2018 at 18:28

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Scott Craner Over a year ago

So, I meant to ask, why the name change?

dom176 Over a year ago

Super helpful and very detailed answer. I'll also be implementing the check you added, didn't realize that was best practice but makes obvious sense.

Collectives™ on Stack Overflow

Using the Find VBA function with Defined String

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related