Selection.Find vs Range(xx).Find??? 1

[Gavona]

I am trying to search within a specific named range in my worksheet.
This works:
Range("B20:h30").select
Selection.Find(........

This doesn't and I don't understand why not:
Range("B20:h30").Find(........
I get a Type Mismatch.

Thanks,

Gavin

 

[SkipVought]

gavin,

Both of these work for me???

Sub test1()
    Dim r As Range
    Set r = Range("B20:h30").Find("Skip")
    r.Select
End Sub
Sub test2()
    Dim r As Range
    Range("B20:h30").Select
    Set r = Selection.Find("Skip")
    r.Select
End Sub

Your actual code.....

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

HI Skip, thanks for responding.

My code is on a laptop and a bit messy to transport. However the issue I have can easily be replicated. In a new workbook the first of these (straight from the recorder) works and the second doesn't.

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

I must be missing something fundamental and basic!

Your code works for me as does Test3 below...but test4 doesn't

Sub Test4()
Range("B20:h30").Find(What:="Skip", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
End Sub
Sub test3()

Range("B20:h30").Find("Skip").Activate

End Sub

Thanks for your help,

Gavin

 

[SkipVought]

What happens if your ActiveCell is WITHIN your specified range or not?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

That's the issue, thanks Skip. I was trying to avoid unnecessary Activation and selection - it would appear that I can't do that here. Is there any rationale for this?

Regards,

Gavin

 

[SkipVought]

The SEARCH is within the specified range as far as I can determine.

You do not necessarily need to use the Select Method if you do something like this...

Sub Test4()
dim r as range
set r = [B20]
Range("B20:h30").Find(What:="Skip", After:=r, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
End Sub

I actually prefer...

Sub Test4()
dim r as range
set r = [B20]
set r = Range("B20:h30").Find(What:="Skip", After:=r, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
if not r is nothing then
  'we found it!!!
end if
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Great, I'll try those. Am I best to avoid Activate (as per your second example) in the same way as one avoids unnecessary Selects? Or is it not the same overhead?

(Have a star for your galaxy)

Regards,

Gavin