Mike,
Pete's solution is elegant and works on dependents in the same sheet. If you
want something that looks outside the sheet you can get a 30 day download of
my XspandXL add-in from my site below which has a lot of range tracing
utilities, including testing precedents/dependents in a range in external
sheets.
Robin Hammond
www.enhanceddatasystems.com
"Pete McCosh" wrote in message
...
Mike,
there is probably a far more elegant solution, but the
following macro will test every entry in column B for
dependents, then fill in column C accordingly.
Sub DependOnIt()
Dim x As Integer
On Error Resume Next
For x = 1 To Application.WorksheetFunction.CountA _
(ActiveSheet.Columns(1))
If IsError(Cells(x, 2).Dependents) Then
Cells(x, 3).Value = "No"
Else
Cells(x, 3).Value = "Yes"
End If
Next x
On Error GoTo 0
End Sub
Cheers, Pete.
-----Original Message-----
I have a long list of 'single point' numerical data items
that a colleague
has extracted from a large written document. This data
has been put into
rows; with column A containiung the data
label/description, and column B
containing the actual data item (be it a general number,
a date, a currency
amount, etc., etc..). I have used many of these elsewhere
within the
workbook, but not all of them so some of the data is
effectively redundant.
Is there an easy way of returning a "Yes" or "No" into
column C, confirming
that the data point has been used elsewhere in the model
(i.e. has
dependents)?
A macro button in the top left of the worksheet that
when run does the
tests and then pastes either a "Yes" or "No" into column
C, as appropriate,
for each row seems to be required, but I do not know what
this would like.
Any help on how to write this would be very much
appreciated.
Thanks
Mike