Circularity Breaker

[JohnnyBetter]

Hi,

Thank you in advance for your help on what seems a very frustrating, but solvable (I hope) problem.

I have a very simple macro that tries to break the circularity on Loan Payment calculations (code below). It is supposed to run until the difference between the Live cell value (referenced from another tab) and the pasted (macro sought value) is zero. For some WEIRD reason, this macro runs FOREVER, although, while it runs, I can see that it found two similar (whole numbers) values in the the Live and Paste cells. Since it runs forever without finding a solution, I stop it. The, if I go into excel and MANUALLY (what the macro should have done AUTOMATICALLY) paste the Live value over the Paste cell value, I solve for the value. After 1 or 2 manual Ctrl+C /Ctrl + V, voila, it works and the difference between the two is 0.

I have two questions:

1. Why isn't the macro running at least a couple of times more to find a solution that it already found? It is supposed to loop until the difference between is 0, yet it stops before the last 1-2 tries.

2. How can I re-write the macro to automatically do the copying/pasting that I manually do to find a solution?

Thank you so much for your help!

Hi,

Thank you in advance for your help on what seems a very frustrating, but solvable (I hope) problem.

I have a very simple macro that tries to break the circularity on Loan Payment calculations (code below). It is supposed to run until the difference between the Live cell value (referenced from another tab) and the pasted (macro sought value) is zero. For some WEIRD reason, this macro runs FOREVER, although, while it runs, I can see that it found two similar (whole numbers) values in the the Live and Paste cells. Since it runs forever without finding a solution, I stop it. The, if I go into excel and MANUALLY (what the macro should have done AUTOMATICALLY) paste the Live value over the Paste cell value, I solve for the value. After 1 or 2 manual Ctrl+C /Ctrl + V, voila, it works and the difference between the two is 0.

I have two questions:

1. Why isn't the macro running at least a couple of times more to find a solution that it already found? It is supposed to loop until the difference between is 0, yet it stops before the last 1-2 tries.

2. How can I re-write the macro to automatically do the copying/pasting that I manually do to find a solution?

Thank you so much for your help!




Sub Loan_Sizing()
Do
Application.Calculation = xlCalculationSemiautomatic
Range("Levrg_paste").Value = Range("Levrg_live").Value
Range("Loan_Paste").Value = Range("Loan_live").Value
Application.CalculateFull
Loop Until Range("Levrg_Check").Value = 0 And Range("Loan_Check").Value = 0
End Sub

[Bob Phillips]

Obviously I cannot see your data so not the whole picture, but wouldn't Goal Seek do what you want without a macro?

[Paul_Hossler]

Just because it looks like a zero, doesn't mean that it's = 0.0000000000000000000000000000

Floating point numbers can be tricky

Maybe just test for <= some epsilon



Loop Until ABS(Range("Levrg_Check").Value) < .001 And Abs(Range("Loan_Check").Value) < .001

[arnelgp]

Sub Loan_Sizing()
Application.CalculateFull
Do
'Application.Calculation = xlCalculationSemiautomatic
Range("Levrg_paste").Value = Range("Levrg_live").Value
Range("Loan_Paste").Value = Range("Loan_live").Value
Calculate
Loop Until Range("Levrg_Check").Value = 0 And Range("Loan_Check").Value = 0
End Sub