First, I am surprised the following alone does not work when there are
no dependents:

dim dep as Range
for each dep in cell.Dependents
[....]
next dep

Normally, the For statement works just fine when it has nothing to do.

But okay, the above produces an error when there are no dependents
("no cells were found").

So what do I test to prevent the error? I thought:

if cell.Dependents.Count 0 then
for each dep in cell.Dependents
[...]
next dep
end if

But that does not work. I also tried IsNull() and IsEmpty(), to no
avail.

So, what's the correct magic incantation to use?

dim dep as Range
If Not dep Is Nothing Then
for each dep in cell.Dependents
[....]
next dep
End If

"joeu2004" wrote:

First, I am surprised the following alone does not work when there are
no dependents:

dim dep as Range
for each dep in cell.Dependents
[....]
next dep

Normally, the For statement works just fine when it has nothing to do.

But okay, the above produces an error when there are no dependents
("no cells were found").

So what do I test to prevent the error? I thought:

if cell.Dependents.Count 0 then
for each dep in cell.Dependents
[...]
next dep
end if

But that does not work. I also tried IsNull() and IsEmpty(), to no
avail.

So, what's the correct magic incantation to use?

I'd use:

Option Explicit
Sub testme()

Dim Dep As Range
Dim AllDeps As Range
Dim Cell As Range

Set Cell = ActiveCell

Set AllDeps = Nothing
On Error Resume Next
Set AllDeps = Cell.Dependents
On Error GoTo 0

If AllDeps Is Nothing Then
'do nothing
Else
For Each Dep In Cell.Dependents
MsgBox Dep.Address
Next Dep
End If

End Sub



joeu2004 wrote:

First, I am surprised the following alone does not work when there are
no dependents:

dim dep as Range
for each dep in cell.Dependents
[....]
next dep

Normally, the For statement works just fine when it has nothing to do.

But okay, the above produces an error when there are no dependents
("no cells were found").

So what do I test to prevent the error? I thought:

if cell.Dependents.Count 0 then
for each dep in cell.Dependents
[...]
next dep
end if

But that does not work. I also tried IsNull() and IsEmpty(), to no
avail.

So, what's the correct magic incantation to use?

--

Dave Peterson

On Feb 14, 6:52*pm, Dave Peterson wrote:
Dim AllDeps As Range
[....]
* * Set AllDeps = Nothing
* * On Error Resume Next
* * Set AllDeps = Cell.Dependents
* * On Error GoTo 0
* * If AllDeps Is Nothing Then

Thanks. I have verified the need for all the elements of your
solution. But I'm still puzzled. If we can set a Range variable
(AllDeps) to Nothing, and if we can test a Range variable (AllDeps)
for "is nothing", why can't we test a property that returns a Range
object (Dependents) for "is nothing" directly?

I know the following does not work:

If cell.Dependents is Nothing Then

But I don't understand why not. If a property returns a Range object,
why doesn't it return Nothing, which seems to be a (null) Range
object, since it can be assigned to a Range variable without error.
(I cannot find an explanation of Nothing.)

I have no idea why it doesn't work.

But if you put a watch on that variable and then step through the code, you'll
see that alldeps doesn't become nothing.



joeu2004 wrote:

On Feb 14, 6:52 pm, Dave Peterson wrote:
Dim AllDeps As Range
[....]
Set AllDeps = Nothing
On Error Resume Next
Set AllDeps = Cell.Dependents
On Error GoTo 0
If AllDeps Is Nothing Then

Thanks. I have verified the need for all the elements of your
solution. But I'm still puzzled. If we can set a Range variable
(AllDeps) to Nothing, and if we can test a Range variable (AllDeps)
for "is nothing", why can't we test a property that returns a Range
object (Dependents) for "is nothing" directly?

I know the following does not work:

If cell.Dependents is Nothing Then

But I don't understand why not. If a property returns a Range object,
why doesn't it return Nothing, which seems to be a (null) Range
object, since it can be assigned to a Range variable without error.
(I cannot find an explanation of Nothing.)

--

Dave Peterson