Confirming existence of dependents

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

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
 
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
Click to expand...
label/description, and column B
 
Pete

Thanks for that - it works fine, but for the fact that if only looks for
dependents on the active sheet. Can it be easily extended to look for any
dependents across all sheets in the workbook?

Cheers

Mike
 
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
 
thanks for your help Pete

Cheers

Mike


Pete McCosh said:
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.
label/description, and column B
Click to expand...
 
Back
Top