I have a worksheet with an inherent circular calculation. To get the answers
to converge, I need to copy D85:W85 to row 55 as Values and hit shift+F9. I
keep doing this (about 5-6 times) till the numbers in row 55 and 85 are less
than 100 off.

Is there a way to automate this process with either a function or a macro?

Thanks in advance,

Anand

From your post it's unclear what process you want automated.

What values/formulae are in row 55, 85?
What's the circular calcuation?


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=470649

Matt:
The worksheet is a part of a large financial model that projects a
partnership's tax capital accounts for the next 20 years.

The amount of non-recouse debt is reduced by the amout of deficit
restoration obligation a partner accepts (Row 85), which in turn depends on
the amount of non-recourse debt. This leads to the circularity.

I need a macro/vba script that can copy the Defit obligation in that is in
row 85 (D85:W85) and paste-special as values only on to Row 55 (D55:W55) and
hit ****-F9 to recalculate the worksheet only. The amount of non-recourse
debt is reduced by the values in Row 55. Once the sheet is recalculated, the
values in Row 85 change. I keep doing this copy-paste-specialValues about 5-6
times and hit shift F9 each time and the difference between Row55 and Row 85
becomes small (say less than 100).

I hope this is clearer ...

-Anand
"MattShoreson" wrote:


From your post it's unclear what process you want automated.

What values/formulae are in row 55, 85?
What's the circular calcuation?


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=470649

Sub Paste_Values()

Application.Calculation = xlCalculationManual
Range("D85:W85").Copy
Range("D55").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Activesheet.Calculate

End Sub

Baapi,
Bangalore


--
Baapi
------------------------------------------------------------------------
Baapi's Profile: http://www.excelforum.com/member.php...o&userid=27333
View this thread: http://www.excelforum.com/showthread...hreadid=470649