Simple macro, even simpler programmer!

[DA]

I'm trying to paste the value of one calculated cell onto another that
just has a vlaue unitl they are essentially the same. The macro below
seems to only iterate once, whereas it usually take three or fout
iterations to get them close. So, clearly something is wrong. Please
help.

Thanks
Dean

Sub Iterate_On_Cost()

Dim Difference As Variant
Dim CalculatedCost As Variant
Dim HardCodedCostValue As Variant


Do
Range("CalculatedCost").Select
Selection.Copy
Range("HardCodedCostValue").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Difference = Abs(CalculatedCost - HardCodedCostValue)
Loop Until Difference < 0.001

End Sub

 

[DA]

I am a little confsued. The algorithm, would normally be circular. I
would have to use iterative calcualtions. I don't like those in very
complex EXCEL files becasue my understanding is that they will also
"solve through" any potential programming errors (e.g, unintended
circular references in other cells).

So, instead of allowing the circulatrity, I use a separate cell to
paste the value into, that separate cell then driving the rest of the
algo. Each time you paste a new value, the source, copied cell
should change again. However, if you keep repeating this copy paste
special values, the source cell begins to change by less and less and
it eventually gets so close to the destinaiton cell that things are
about equal. Then, I'm done.

It is set on auto recalc but maybe, somehow, within the macro, it
doesn't see the spreasdsheet recalcing new vlaues, so it thinks it's
done. Or maybe my aboslute vlaue cal is in the wrong placwe within
the macro?

Does that help make some sense of this?

Thanks again!
Dean

 

[Bernie Deitrick]

DA,

Try it like this: I've assumed you have named ranges CalculatedCost and
HardCodedCostValue.

Sub Iterate_On_Cost()

Dim Difference As Double

Difference = 1
While Difference > 0.001
Range("HardCodedCostValue").Value = _
Range("CalculatedCost").Value
Application.Calculate
Difference = Abs(Range("CalculatedCost").Value - _
Range("HardCodedCostValue").Value)
Wend

End Sub


HTH,
Bernie
MS Excel MVP

 

[DA]

Yes, I do have these cells named. This seems to get to the right
value almost instanteously, but it just keeps calculating and the
macro never stops. If I set up a cell in the worksheet eqaul to the
differecne, I notice that it never can get below 0.0037, so if I set
the threshold to 0.01, I won't have a problem. I'm suprised becasue I
have the max change cell set ot 0.000001. You'd think it could get
closer. Any ideas< though I guess this is good enough?

Thanks!


For "smartin", I see your profile shows "finance". Bascially, I want
to borrow 50% of cost including the interest on the loan. That's the
application.

 

[Bernie Deitrick]

DA,

It all depends on your formulas and values.... some require a different
approach to converge on a value, especially models that are not linear or
nearly so - then it is easy to get into loops where the values simply
oscillate back and forth. You could add a counter that quits the loop after
a certain number of attempts are made.

I was just attempting to show you the proper coding for your example, but if
you want to attempt using higher order techniques, there is a world of code
available through your good friend Google. ;-)

HTH,
Bernie
MS Excel MVP

 

[DA]

Yes, I should know this as I'm a mathematician. This just did not
seem that complex an algorithm. I think the 0.01 tolerance is plenty
good enough.

Thanks much!
Dean