Excel VBA: Runtime error 424, Object required

[BobAtVandy]

I have a range of cells selected in an Excel 2003 worksheet, and the
following macro works:

Sub Macro1()
Selection.Find(What:="4", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
End Sub

The following macro fails with "Runtime error '424', Object required".
The only difference is the Dim statement and "set r =". I've also tried
"Dim r As Object", which fails the same way.

Sub Macro1()
Dim r As Range
Set r = Selection.Find(What:="4", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
End Sub

I'm a newbie at this, but I've googled extensively and nothing I see
resolves it. In fact, I find examples of code that supposedly work and look
just like what I've got, which fails.

Any tips/help would be appreciated. Thanks.

 

[Steve Rindsberg]

Try leaving off the final .Activate

I have a range of cells selected in an Excel 2003 worksheet, and the
following macro works:

Sub Macro1()
Selection.Find(What:="4", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
End Sub

The following macro fails with "Runtime error '424', Object required".
The only difference is the Dim statement and "set r =". I've also tried
"Dim r As Object", which fails the same way.

Sub Macro1()
Dim r As Range
Set r = Selection.Find(What:="4", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
End Sub

I'm a newbie at this, but I've googled extensively and nothing I see
resolves it. In fact, I find examples of code that supposedly work and look
just like what I've got, which fails.

Any tips/help would be appreciated. Thanks.