chrnit
chrnit

Reputation: 21

Trouble with vba error 91 when using With, Range and .Find

i would like to find the address of the cell containing the value "USD" in below mentioned code. though, the system throws me an error 91 saying that an object variable has not been set. i found some info online on error 91 but i still don't get where and how to set the right object. help is appreciated.

thanks

Sub searchAdress()
 Dim searchAdress As Range
 With Workbooks("Umrechnungskurse1.xlsm").Sheets("Tabelle1").Range("A2:S2")
    searchAdress = .Find("USD", LookIn:=xlValues)
 End With
 MsgBox searchAdress
End Sub

Upvotes: 0

Views: 168

Answers (1)

Mr. Mascaro
Mr. Mascaro

Reputation: 2733

The first problem is that your line:
searchAdress = .Find("USD", LookIn:=xlValues)

Should be :
Set searchAdress = .Find("USD", LookIn:=xlValues)
The Set command is required for object variables.

Your next problem is that your MsgBox will not work. Change the line to:
MsgBox searchAdress.Address

Upvotes: 1

Related Questions