misbehaving DirectPrecedents

[JimmyTheHand]

Hi all,

On another forum, trying to help someone with a function, I got stuck because of a strange problem. See the attached example workbook for reference.
Column C has a very simple UDF, which should give me addresses of cells from column A. Instead, it gives addresses of cells from column B. Apparently, DirectPrecedents property doesn't work well here.

On the other hand, clicking on the Show it button shows the correct addresses, even though it uses the very same DirectPrecedents property.

Any ideas why it is so?

Thanks in advance,

Jimmy

[rory]

I don't really know why it should fail but it certainly does! I can only guess it has something to do with the way the precedents are determined and the interaction with the UI - but that is a guess! :)
Rory

[rory]

Seems

[rory]

Seems to be borne out by that fact that if you rewrite

[rory]

Seems to be borne out by that fact that if you rewrite the sub to use the function, it works; but the same

[rory]

Seems to be borne out by that fact that if you rewrite the sub to use the function, it works; but the same function does not work in the worksheet:
Function test(ByRef rng As Range) As String
test = rng.DirectPrecedents.Address
End Function
Sub ShowDirectPrecedents()
Dim c As Range
For Each c In Range("B1:B3")
c.Offset(, 3) = test(c)
Next
End Sub

Regards,
Rory

[rory]

OK

[rory]

OK that was weird - I only pressed Post o

[JimmyTheHand]

OK that was weird - I only pressed Post oYeah... It was suspicious to see 7 Replies and 8 Views next to my thread :devil2:

So now it's confirmed by both of us. Anyone have an idea about how to counter this problem?

BTW, the original problem was to have column D updated all the time, based on column C, following this logic:

if C is then D is
=A1 =E1
=A2 =E2
=A1 & A2 =MAX(E1,E2)
=A3 & A5 =MAX(E3,E5)
My solution would have been this UDF:
Function MaxB(Rng As Range)
MaxB = Application.WorksheetFunction.Max(Rng.DirectPrecedents.Offset(, 4))
End Function
If nobody knows how to prevent DirectPrecedents from going astray, I (as well as the original poster) would be happy with an alternative solution.

[rory]

You might try using a sub called from the worksheet_change event to update the formulas in D when the formulas in C are changed?
Regards,
Rory