A B C D
Cur Prchs Prev Cur
Prc Prc Max Max
==== ==== ==== ====
10 8 11 11
12 13 13 13
15 12 13 15


Col A, Current Price, changes daily
Col B, Purchase price is constant
Col C, Previous Max from yesterday
Col D, Current Max = MAX(A,B,C)

If C is less than D, then Copy D to C
Using Excel functions, this results in a circular error
The solution is a piece of cake in Fortran (remember that?), but my
VBA isn't up to it.
Help!!!

- bud -

Let me guess, you tried using C1=IF(C1<D1,D1,C1) right?
The reason you get an error is that Excel worksheet
functions work like algebra, not like program code, so
A=A+1 is algbraically impossible. However, there is a
solution to allow circular references to work without
creating an error. Go to the Tools menu and select
Options, click the Calculations tab, then check the
Iterations check box.

But then another problem arrises. As soon as the formula
in D recalculates, so does the formula in C, so C will
always be greater than or equal to D. OK, so to prevent
this, then you will have to turn automatic calculations
off; again from Tools/Options/Calculation but check Manual
this time. Also, by default Excel calculates everything
when you save, so you can deselect this option from the
same place.

Now you will need a way to recalculate D without affecting
C when the value in column A changes. Add this to you
worksheet's code module (right-click the worksheet tab,
click View Code, paste this)

Private Sub Worksheet_Change(ByVal Target As Range)
Application.Calculation = xlCalculationManual
If Target.Column = 1 Then
Range("D:D").Calculate
End If
End Sub

And even with this done, it's prone to problems because if
a full recalculation is done at any time after the sheet
is open, C will be equal to or greater than D.

It may be easier to simply copy the values in D manually
over to C at the beginning of the day and forget
everything I mentioned above.




-----Original Message-----
A B C D
Cur Prchs Prev Cur
Prc Prc Max Max
==== ==== ==== ====
10 8 11 11
12 13 13 13
15 12 13 15


Col A, Current Price, changes daily
Col B, Purchase price is constant
Col C, Previous Max from yesterday
Col D, Current Max = MAX(A,B,C)

If C is less than D, then Copy D to C
Using Excel functions, this results in a circular error
The solution is a piece of cake in Fortran (remember
that?), but my
VBA isn't up to it.
Help!!!

- bud -


.