How to set variable to nothing
 

Excel 2002, WinXP
I have the following code within a loop.

On Error Resume Next
Sht = ClientList.Find(What:=.............
On Error GoTo 0
If Sht Is Nothing Then.................

My problem is that, because this in a loop, the variable "Sht" will retain
its previous value if the Find statement cannot find the search item.
Therefore, the "If Sht Is Nothing Then....." statement will not do what I
want.

My question is:
What can I set as the value of Sht, before the above code, to have the "If"
statement react as I want? Sht = Nothing doesn't do it.

Or a more general question:
How can I determine that the Find statement found nothing?

I know that I can use "On Error GoTo Somewhere" but is there a better way?
Thanks for your help. Otto

How to set variable to nothing
 

Only object variables can have a value of Nothing. One way:

Dim rFound As Range
On Error Resume Next
Set rFound = ClientList.Find(...
On Error GoTo 0
If rFound Is Nothing Then
MsgBox "Not Found"
Else
'Do something else
End If


In article ,
"Otto Moehrbach" wrote:

Excel 2002, WinXP
I have the following code within a loop.

On Error Resume Next
Sht = ClientList.Find(What:=.............
On Error GoTo 0
If Sht Is Nothing Then.................

My problem is that, because this in a loop, the variable "Sht" will retain
its previous value if the Find statement cannot find the search item.
Therefore, the "If Sht Is Nothing Then....." statement will not do what I
want.

My question is:
What can I set as the value of Sht, before the above code, to have the "If"
statement react as I want? Sht = Nothing doesn't do it.

Or a more general question:
How can I determine that the Find statement found nothing?

I know that I can use "On Error GoTo Somewhere" but is there a better way?
Thanks for your help. Otto

How to set variable to nothing
 

set sht = nothing
(simpler than you thought, huh?)

or:

dim FoundCell as range

set foundcell = worksheets("sheet1").cells.find(....)

if foundcell is nothing then
'it's not found
else
msgbox foundcell.address
end if

You don't need the on error stuff and for the .find, you don't need to set it to
nothing first (but it won't hurt too much--I've been corrected a few times for
having that in code I've posted to the ng. <bg).


Otto Moehrbach wrote:

Excel 2002, WinXP
I have the following code within a loop.

On Error Resume Next
Sht = ClientList.Find(What:=.............
On Error GoTo 0
If Sht Is Nothing Then.................

My problem is that, because this in a loop, the variable "Sht" will retain
its previous value if the Find statement cannot find the search item.
Therefore, the "If Sht Is Nothing Then....." statement will not do what I
want.

My question is:
What can I set as the value of Sht, before the above code, to have the "If"
statement react as I want? Sht = Nothing doesn't do it.

Or a more general question:
How can I determine that the Find statement found nothing?

I know that I can use "On Error GoTo Somewhere" but is there a better way?
Thanks for your help. Otto

--

Dave Peterson

How to set variable to nothing
 

Otto,

As an object variable, you Set it, both to it's initial value and to reset
to nothing.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I have the following code within a loop.

On Error Resume Next
Sht = ClientList.Find(What:=.............
On Error GoTo 0
If Sht Is Nothing Then.................

My problem is that, because this in a loop, the variable "Sht" will retain
its previous value if the Find statement cannot find the search item.
Therefore, the "If Sht Is Nothing Then....." statement will not do what I
want.

My question is:
What can I set as the value of Sht, before the above code, to have the
"If"
statement react as I want? Sht = Nothing doesn't do it.

Or a more general question:
How can I determine that the Find statement found nothing?

I know that I can use "On Error GoTo Somewhere" but is there a better way?
Thanks for your help. Otto

How to set variable to nothing
 

JE, Dave, Bob
Thanks for the help. Otto
"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I have the following code within a loop.

On Error Resume Next
Sht = ClientList.Find(What:=.............
On Error GoTo 0
If Sht Is Nothing Then.................

My problem is that, because this in a loop, the variable "Sht" will retain
its previous value if the Find statement cannot find the search item.
Therefore, the "If Sht Is Nothing Then....." statement will not do what I
want.

My question is:
What can I set as the value of Sht, before the above code, to have the
"If"
statement react as I want? Sht = Nothing doesn't do it.

Or a more general question:
How can I determine that the Find statement found nothing?

I know that I can use "On Error GoTo Somewhere" but is there a better way?
Thanks for your help. Otto