Not understanding If Not..Then nothing
 

I'm confused by using an "If not then nothing". It seems to me that if
I do a find, and the text I searched for is found, then the value of
the range it is found in would NOT be nothing. But it is. So is that
correct, that when XL finds what it's looking for, the value of that
range is "nothing"? I won't be surprised much if it's the opposite of
what makes sense to me. Everything else in VBA is!
Thanks for your help.

Not understanding If Not..Then nothing
 

I did a more testing. It's always nothing! If it finds the text, the
range variable is nothing. If it doesn't find the text, the range
variable is nothing.
That begs the question: If you do a "Find" in a macro, how do you know
if a cell with that value was found? I copied a "If Not rangevariable
is Nothing" from another thread which I thought would tell me if the
value had been found. I guess it does something else. So, after you do
a find, how do you know if it was found or not?
Thanks!

Not understanding If Not..Then nothing
 

davegb,

Your question seems a bit confusing. I use the find method quite
frequently, and xl does not return an empty (Nothing) variable when the value
is actually found, (Maybe xl is not actually finding it in your tests.)
Ben

--
When you lose your mind, you free your life.


"davegb" wrote:

I did a more testing. It's always nothing! If it finds the text, the
range variable is nothing. If it doesn't find the text, the range
variable is nothing.
That begs the question: If you do a "Find" in a macro, how do you know
if a cell with that value was found? I copied a "If Not rangevariable
is Nothing" from another thread which I thought would tell me if the
value had been found. I guess it does something else. So, after you do
a find, how do you know if it was found or not?
Thanks!

Not understanding If Not..Then nothing
 

Ben, Thanks for your reply.
Yes, XL is finding it. It's just still saying that FoundCell is
nothing.
Dave

ben (remove this if mailing direct) wrote:
davegb,

Your question seems a bit confusing. I use the find method quite
frequently, and xl does not return an empty (Nothing) variable when the value
is actually found, (Maybe xl is not actually finding it in your tests.)
Ben

--
When you lose your mind, you free your life.


"davegb" wrote:

I did a more testing. It's always nothing! If it finds the text, the
range variable is nothing. If it doesn't find the text, the range
variable is nothing.
That begs the question: If you do a "Find" in a macro, how do you know
if a cell with that value was found? I copied a "If Not rangevariable
is Nothing" from another thread which I thought would tell me if the
value had been found. I guess it does something else. So, after you do
a find, how do you know if it was found or not?
Thanks!

Not understanding If Not..Then nothing
 

Here's my code:

'Test for Client Disab
On Error Resume Next
RecSht.Range("1:1").Select
FoundCell = Cells.find(What:="Client Disab", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False)

If Not FoundCell Is Nothing Then
RecSht.Range("aa4").Copy Destination:=CtyExtr.Range("N5")

I've done a watch on "FoundCell", dimmed as range,on datasheets with
and without the searched for text ("Client Disab"). Whether the text is
found or not, FoundCell is nothing.
Any suggestions?

Not understanding If Not..Then nothing
 

You're missing a Set command.

FoundCell = Cells.find(What:="Client Disab",
After:=ActiveCell,
should be
Set FoundCell = Cells.find(What:="Client Disab",
After:=ActiveCell,



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"davegb" wrote in message
oups.com...
Here's my code:

'Test for Client Disab
On Error Resume Next
RecSht.Range("1:1").Select
FoundCell = Cells.find(What:="Client Disab",
After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext,
_
MatchCase:=False)

If Not FoundCell Is Nothing Then
RecSht.Range("aa4").Copy
Destination:=CtyExtr.Range("N5")

I've done a watch on "FoundCell", dimmed as range,on datasheets
with
and without the searched for text ("Client Disab"). Whether the
text is
found or not, FoundCell is nothing.
Any suggestions?

Not understanding If Not..Then nothing
 

That did it! Thanks, Chip!

Chip Pearson wrote:
You're missing a Set command.

FoundCell = Cells.find(What:="Client Disab",
After:=ActiveCell,
should be
Set FoundCell = Cells.find(What:="Client Disab",
After:=ActiveCell,



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"davegb" wrote in message
oups.com...
Here's my code:

'Test for Client Disab
On Error Resume Next
RecSht.Range("1:1").Select
FoundCell = Cells.find(What:="Client Disab",
After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext,
_
MatchCase:=False)

If Not FoundCell Is Nothing Then
RecSht.Range("aa4").Copy
Destination:=CtyExtr.Range("N5")

I've done a watch on "FoundCell", dimmed as range,on datasheets
with
and without the searched for text ("Client Disab"). Whether the
text is
found or not, FoundCell is nothing.
Any suggestions?