CODEWITHSUNDEEP
excelvba
python noobie
python noobie

Reputation: 109

Find a variable with vba

I want to set the cell value as a variable an then use ctrl+f to find it in the spreadsheet, but I'm getting this message: Object variable or with block variable not set.

For instance, the cell A2 has the value of 1. I want that VBA look for 1 using the variable name.

Please see my code below:

Sub find ()

Range("A1").Select
Dim amount As String
amount = Range("A2")

Cells.find(What:="amount", After:=ActiveCell, LookIn:=xlFormulas2, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

End sub()

Upvotes: 0

Views: 108

Answers (1)

dbmitch
dbmitch

Reputation: 5386

The issue is that you've enclosed your variable in quotation marks - that in effect just treats it as the literal string "amount"

Simple answer is to remove quotation marks around any variable name Use amount standalone

If you need to enclose a variable in a string you can concatenate and sandwich the variable between quoted strings. As in, "my amount is: " & amount

Upvotes: 1

Related Questions