set and then select
 

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.
--
David

But
 

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.
--
David


"David" wrote:

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.
--
David

But
 

Hi David,

You can test FoundCell with something like this:

If Not FoundCell Is Nothing Then
'a match was found.
FoundCell.Activate
End If



--
Hope that helps.

Vergel Adriano


"David" wrote:

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.
--
David


"David" wrote:

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.
--
David

But
 

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,
--
David


"Vergel Adriano" wrote:

Hi David,

You can test FoundCell with something like this:

If Not FoundCell Is Nothing Then
'a match was found.
FoundCell.Activate
End If



--
Hope that helps.

Vergel Adriano


"David" wrote:

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.
--
David


"David" wrote:

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.
--
David

But
 

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



--
Hope that helps.

Vergel Adriano


"David" wrote:

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,
--
David


"Vergel Adriano" wrote:

Hi David,

You can test FoundCell with something like this:

If Not FoundCell Is Nothing Then
'a match was found.
FoundCell.Activate
End If



--
Hope that helps.

Vergel Adriano


"David" wrote:

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.
--
David


"David" wrote:

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.
--
David

But
 

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


"David" wrote:

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.
--
David


"David" wrote:

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.
--
David