Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple macro, even simpler programmer!
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple macro, even simpler programmer!
Step through your code using F8 and watch what happens:
DA wrote: 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 At this point you have copied worksheet range CalculatedCost and pasted it over the old value in worksheet range HardCodedCostValue. So now they are equal (look at your worksheet). And note, you have not assigned anything to your variables. Application.CutCopyMode = False Difference = Abs(CalculatedCost - HardCodedCostValue) Take a look at your variables. They all =0 because you never assigned a value to the variables CalculatedCost and HardCodedCostValue. Note that the coincidence of naming ranges and variables the same does not transfer any values, only confuses things (^: Loop Until Difference < 0.001 The loop ends on the first pass because Difference = 0. End Sub I'm not sure what you are trying to accomplish, but hopefully this gives you some ideas. Post back if you need more help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple macro, even simpler programmer!
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple macro, even simpler programmer!
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple macro, even simpler programmer!
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple macro, even simpler programmer!
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" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple macro, even simpler programmer!
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A simpler way | Excel Programming | |||
Simpler Macro | Excel Programming | |||
is there not a simpler way | Excel Programming | |||
ot - Wanted: Excel Programmer Writers in RedmondWanted: Excel Programmer Writers in Redmond | Excel Programming | |||
How to run a macro many times simpler? | Excel Programming |