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

Question

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

`.Find` inherits many parameters from the last time it was used. Do you want `XlPart` or `xlWhole`? There are several others but that is likely the most important. — , Nov 06 '14 at 16:28
See the first example here: http://msdn.microsoft.com/en-us/library/office/ff839746(v=office.15).aspx — bp_, Nov 06 '14 at 16:30
http://stackoverflow.com/questions/3872339/what-is-the-difference-between-dim-and-set-in-vba — Siddharth Rout, Nov 06 '14 at 16:30
Offtopic: Also since you are using `.Find`, you may find [this](http://www.siddharthrout.com/2011/07/14/find-and-findnext-in-excel-vba/) topic interesting — Siddharth Rout, Nov 06 '14 at 16:33
thanks guys, it was the missing set command. reading your linked articles now. — chrnit, Nov 06 '14 at 16:37

Mr. Mascaro · Accepted Answer · 2014-11-06T17:03:08.613

1

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

edited Nov 06 '14 at 17:03

answered Nov 06 '14 at 16:55

Mr. Mascaro

2,693
1
11
18

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

1 Answers1

Linked