Solved: Dependents and Precedents

[PSL]

Hi again,

As a part of a macro I am trying to figure out the functionality of Dependents and precedents. I've pretty much understood the concept of Precedents and have been able to use them in macros.

So this macro i'm writing would have 2 basic conditions:
1. Precedents.Count = 0
2. Dependents.Count <> 0


If Range("A1").Precedents.Count = 0 _
And _
Range("A1").Dependents.Count <> 0 _
Then

.....

Else
....

End If



Basically, the macro should test that, firstly, A1 is not a formulae itself and secondly, alteast one of the cells in the active worksheet is linked to A1.

Now I'm able to get the precedents thing to work. But the 'Dependents' is not working. Been tryin to look over the net for almost an hour. Rather new at this mind you :(

Where am I going wrong?

Thanks in advance

PSL

[Bob Phillips]

If it doesn't have precedents or dependents it will error so you should capture those values into a variable wrapped in error resume, then test the variables.

[PSL]

If it doesn't have precedents or dependents it will error so you should capture those values into a variable wrapped in error resume, then test the variables.

Did try doing something on the followin lines..

On Error Resume Next

Pre = Range(ActiveCell.Offset(0, 0).Value & "6").Precedents.Count
Dep = Range(ActiveCell.Offset(0, 0).Value & "6").Precedents.Count

If Pre = 0 And Dep <> 0 And Range(ActiveCell.Offset(0, 0).Value & "6").Value <> "" Then

........



Else
...........

End If



Now assuming that ActiveCell.value is A. So this macro will check the precedents and dependents of A6.

Now the issue is that I intend to loop this macro. As in

ActiveCell.Offset(0, 1).Select


And now the value in the Active cell would be B. And (ideally) Pre and Dep should be according to B6. However the values of Pre and Dep don't change. As in they remain according to A6.

So the main issue is that the values of Pre and Dep, once set do not change. Is there a way around it?

I hope I was clear enough. :)

cheers..
PSL

[Bob Phillips]

Dim Pre As Long, Dep As Long

On Error Resume Next
Pre = Cells(6, ActiveCell.Column).Precedents.Count
Dep = Cells(6, ActiveCell.Column).Dependents.Count
On Error GoTo 0

If Pre = 0 And Dep <> 0 And Cells(6, ActiveCell.Column).Value <> "" Then

'........
Else

'...........
End If

[PSL]

Yup, that seems to do the trick!

thanks a lot!

[PSL]

Hey,
Back again!

Just a small addition to the macro I'm working on.

Is is possible for a macro to tell if any one of the dependents are -ve?

something like: dependents.Value < 0

can't think how how the dependents property would deal with it?

Or is there some other way it could happen?

Regards,
PSL

[mdmackillop]

Sub Negs()
dep = Cells(5, 4).Dependents.Count
For Each cel In Cells(5, 4).Dependents
If cel.Value < 0 Then cnt = cnt + 1
Next
MsgBox dep & " - " & cnt
End Sub

[PSL]

Sub Negs()
dep = Cells(5, 4).Dependents.Count
For Each cel In Cells(5, 4).Dependents
If cel.Value < 0 Then cnt = cnt + 1
Next
MsgBox dep & " - " & cnt
End Sub



Hi,

Thanks!.. Modified it a bit for my purpose. Having a small problem.

Sub Negs()


Dim cel As Range

Do While ActiveCell.Value <> "%%%"

tt = 0

'pre = Cells(5, 4).Precedents.Count
For Each cel In Range(ActiveCell.Offset(0, tt).Value & "5").Precedents
If cel.Value < 0 Then cnt = cnt + 1


Next

Range(ActiveCell.Offset(0, tt).Value & "2").Value = cnt
ActiveCell.Offset(0, 1).Select

tt = tt + 1

Loop


End Sub

So ideally, value in row 2 should keep chaging as the loop runs. However it stays the same with respect to the where I start the macro.
Sorry if it sound to 'noob'.

Have attached the file as well.

Thanks and Regards,
PSL

[mdmackillop]

You need to reset the counter to 0
ActiveCell.Offset(0, 1).Select
cnt = 0
tt = tt + 1

[PSL]

Oh!,

I knew I was missing something basic. Thanks a ton as usual.

Regards,
PSL

*SOLVED*

[mdmackillop]

A neater way to loop through the headers, avoiding the need for the "Stop" cell

Sub Negs()
Dim cel As Range
Dim hCel As Range
Dim Rng As Range
Set Rng = Range(Cells(1, 4), Cells(1, Columns.Count).End(xlToLeft))
For Each hCel In Rng
For Each cel In hCel.Offset(4).Precedents
If cel.Value < 0 Then cnt = cnt + 1
Next
hCel.Offset(1).Value = cnt
cnt = 0
Next
End Sub

[PSL]

Set Rng = Range(Cells(1, 4), Cells(1, Columns.Count).End(xlToLeft))

Quite an interesting approach!.

But often there are blank cells in between, where I simply put a code to skip them. By using an xlToLeft, I suppose it'll stop midway.

Neat approach though, will definitely try to remember it the next time i'm trying to do something similar.

Thanks and regards!
PSL

[mdmackillop]

This method starts from the rightmost column in any Excel version and finds the rightmost filled cell, thus avoiding the problem of blanks.
For Rows use


Set Rng = Range(Cells(1, 1), Cells(rows.count, 1).End(xlUp))