Pls help when "for each" is empty

[joeu2004]

I have the following VB macro fragment:

for each cell in Selection
for each p in cell.Precedents
debug.print "p=" & typename(p)
next p
next cell

When the selected cell has =1+2, for example, "for each p" fails with
the error "no cells were found" because "p" is Empty.

I remedy the error by adding the statement "on error resume next".

However, the debug.print statement is still executed(!). I expected
execution to go to the "next p" statement and fall out of the loop
because "p" is Empty.

I tried to avoid the problem by adding the statement "if IsEmpty(p)
then exit for" after "for each p".

But the debug.print statement is __still__ executed (!). The "exit
for" statement is executed, but apparently it is treated as an
error(!), and presumably the "on error resume next" statement takes
effect.

I tried to avoid __that__ problem by adding the statement "on error
goto 0" before "exit for", in addition to other required syntax
changes (see endnotes).

But then I get the error "for loop not initialized".

The only thing that seems to work is "if IsEmpty(p) then goto done".

But I would prefer something like "exit for" for aesthetic reasons,
especially when I have several nested for-loops that might encounter
this condition (empty "for each"), which would require labels like
"done1", "done2", etc or other distinctive labels.

Am I missing something? Or is a "goto" the only way to abort an empty
"for each" loop?


-----

Endnotes

1. Preferred solution:

on error resume next
for each cell in Selection
for each p in cell.Precedents
if IsEmpty(p) then exit for
debug.print "p=" & typename(p)
next p
next cell


2. Failed solution:

for each cell in Selection
on error resume next
for each p in cell.Precedents
if IsEmpty(p) then
on error goto 0
exit for
end if
debug.print "p=" & typename(p)
next p
next cell


3. Undesirable working solution:

on error resume next
for each cell in Selection
for each p in cell.Precedents
if IsEmpty(p) then goto Done
debug.print "p=" & typename(p)
next p
Done:
next cell

 

[Norman Jones]

Hi Joeu,

Try something like:

'=============>>
Public Sub Tester()
Dim rCell As Range
Dim rngPrecedents As Range
Dim pCell As Range

For Each rCell In Selection
On Error Resume Next
Set rngPrecedents = rCell.Precedents
On Error GoTo 0
If Not rngPrecedents Is Nothing Then
For Each pCell In rCell.Precedents
Debug.Print "p=" & TypeName(pCell)
Next pCell
End If
Next rCell
On Error GoTo 0
End Sub
'<<=============

However, will the precedents not always return
'Range'?

 

[Norman Jones]

Hi Joeu,

Please delete the penultimate line:

On Error GoTo 0