Not understanding If Not..Then nothing

D

davegb

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

davegb

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!
 
G

Guest

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
 
D

davegb

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

davegb

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?
 
C

Chip Pearson

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
 
D

davegb

That did it! Thanks, Chip!

Chip said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top