set and then select

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi group,

I am doing a find for text. I thought i had to Set as a range, then do the
"Find" then select it, but I am getting an error.
This is the code I have that produces the Error:
Dim FoundCell As Range
Set FoundCell = Selection.Find(What:="ORDER:", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Any help would be appreciated.
 
Hi again,
I got part of this, but it is part of a loop. I was able to select the cell
in the first instance of the find, but when the loop goes around again, it
fails. The loop includes the Set and Find again, but the FoundCell has
nothing in it at this point, so it can not be activated.

New Code:
Dim FoundCell As Range
Set FoundCell = Selection.Find(What:="ORDER:", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
Range(FoundCell.Address).Activate <-This is now where it fails.
 
Hi David,

You can test FoundCell with something like this:

If Not FoundCell Is Nothing Then
'a match was found.
FoundCell.Activate
End If
 
Hi Again,
I am able to Select the cell on the first instance, but in the loop on the
second instance it fails. "Object Variable or With block variable not set"

Thanks,
 
Hi David,

I'm not sure what you mean by being able to select on the first instance as
there is no loop in the code that you provided. But basically, the reason
you get the error is if Find does not find any match, it returns Nothing.
Using the code you provided, try it this way:

Dim FoundCell As Range
Set FoundCell = Selection.Find(What:="ORDER:", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
'Range(FoundCell.Address).Activate <-This is now where it fails.
If Not FoundCell is Nothing Then
FoundCell.Activate
End if
 
Maybe you should be using the FindNext method:
myRange = Range("WhateverYouWant")
With Worksheets(1).Range(myRange)
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
 
Back
Top