Prev: auto fill or if function
Next: Search and then format by color
From: Dave O on 12 Apr 2010 23:39
Does VBA code exist that returns a True when a cell has one or more
dependents?

Thanks
From: Jim Cone on 13 Apr 2010 01:01
You have to roll your own.
There is a HasFormula property for a cell.
There is also a Precedents and a Dependents property that returns a Range object.
So...

Function AreThereKids(ByRef CellPassedIn As Range) As Boolean
Dim objRng As Range
On Error Resume Next
Set objRng = CellPassedIn.Dependents
On Error GoTo 0
AreThereKids = Not objRng Is Nothing
End Function
--
Jim Cone
Portland, Oregon USA
(xl2003 color sorting... http://www.contextures.com/excel-sort-addin.html)



"Dave O" <CycleZen(a)yahoo.com>
wrote in message news:5c54344a-a745-48c6-a48f-41e492d206fa(a)z9g2000vbm.googlegroups.com...
Does VBA code exist that returns a True when a cell has one or more
dependents?

Thanks
From: Dave O on 13 Apr 2010 13:03
Jim-
Thanks for your note. Not to be picky: is there a way to write that
same code as a routine, rather than a function? Here's my deal: I've
written code (below) to check if a range contains any values (so I can
know whether it is safe to delete). I'd like to incorporate code that
checks each cell to see if that cell has dependents, so I know not to
delete the cell even if it is empty (since deleting the cell causes a
#REF! error in dependent cells).

Your suggestion is excellent- however if I use the function in a
spreadsheet cell, the function itself becomes a dependent of the cell,
and returns a false positive.

Any ideas?

code follows:
Sub WhatsInRange()
Dim rCell As Range
For Each rCell In Selection
If rCell.Value <> "" Then
Range(rCell.Address).Select
End
End If
Next rCell
MsgBox "Nothing found."
End Sub

Thanks again,
Dave O
From: Jim Cone on 13 Apr 2010 14:13
You can call functions/subs from functions or subs.
'--
Sub WhatsInRange_R1()
Dim rCell As Range
For Each rCell In Selection.Cells
If IsEmpty(rCell) Then
If AreThereKids(rCell) = False Then
Range(rCell.Address).Select
Exit Sub
End If
End If
Next 'rCell
MsgBox "Nothing found."
End Sub

Function AreThereKids(ByRef CellPassedIn As Range) As Boolean
Dim objRng As Range
On Error Resume Next
Set objRng = CellPassedIn.Dependents
On Error GoTo 0
AreThereKids = Not objRng Is Nothing
End Function
--
Jim Cone
Portland, Oregon USA
(free excel add-in to remove excess Styles or Cond. Formats... http://excelusergroup.org/media/p/4861.aspx )





"Dave O" <CycleZen(a)yahoo.com>
wrote in message news:88b45fb7-e365-4e0f-9f83-91fdd6a812d1(a)c36g2000yqm.googlegroups.com...
Jim-
Thanks for your note. Not to be picky: is there a way to write that
same code as a routine, rather than a function? Here's my deal: I've
written code (below) to check if a range contains any values (so I can
know whether it is safe to delete). I'd like to incorporate code that
checks each cell to see if that cell has dependents, so I know not to
delete the cell even if it is empty (since deleting the cell causes a
#REF! error in dependent cells).

Your suggestion is excellent- however if I use the function in a
spreadsheet cell, the function itself becomes a dependent of the cell,
and returns a false positive.

Any ideas?

code follows:
Sub WhatsInRange()
Dim rCell As Range
For Each rCell In Selection
If rCell.Value <> "" Then
Range(rCell.Address).Select
End
End If
Next rCell
MsgBox "Nothing found."
End Sub

Thanks again,
Dave O
From: Dave O on 13 Apr 2010 16:32
Your kung fu is vastly superior!

Thanks for your help~

Dave O
 | 
Pages: 1
Prev: auto fill or if function
Next: Search and then format by color