thanx in advance for any help on this issue. I'm not a programmer, so
the code I've provided is likely very inefficient. my question is as
follows. I do a great deal of "scenario" analysis. it would be
beneficial to be able to change the "strategy" and copy the relevant
results to a certain location without having to copy numerous lines of
code for each scenario I'm testing. an example is below.

I take a "strategy" called "equity" and subtract the base case from it,
subsequently storing the results. my base case "equity" is 1, all
others are triggered by the numbers 2 - 12 in this instance. my result
table is a series of columns with names that go from "Res_ED_60" to
"Res_ED_70" with only the last 2 digits changing. to help with this
making a bit more sense, the results are simply (Res)ult (E)quity
(D)etermination (60) % of equity. the results are always copied from
"Res_ED". it would seem to me that I can put this in some type of loop
where the only things changing are the "equity" scenario in line 2 of
the code, and the relevant result set name in line 22 of the code. in
the event the lines get word wrapped incorrectly, I copy/pasted the 2
lines I think are relevant immediately below.
---------------------------------
ActiveCell.FormulaR1C1 = "3"
Application.Goto Reference:="Res_ED_61"
---------------------------------

Application.Goto Reference:="equity"
ActiveCell.FormulaR1C1 = "3"
Calculate
Application.Goto Reference:="active_equity"
Selection.Copy
Application.Goto Reference:="base_equity"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="active_label"
Selection.Copy
Application.Goto Reference:="base_label"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="equity"
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "1"
' copies ED cumulative imbalance results to results table
Application.Goto Reference:="Res_ED"
Selection.Copy
Application.Goto Reference:="Res_ED_61"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Thank you again for any help on this issue. I'm hoping it's a fairly
easy task.


--
wingfield
------------------------------------------------------------------------
wingfield's Profile: http://www.excelforum.com/member.php...o&userid=12968
View this thread: http://www.excelforum.com/showthread...hreadid=536477

It is almost impossible to read recorded code like that, so try this on

Sub TestLoop()
for i = 60 to 70
application.Goto Reference:=Range("Res_ED_" & i)
msgbox "Res_ED_" & i
Next
end Sub

--
Regards,
Tom Ogilvy


"wingfield" wrote:


thanx in advance for any help on this issue. I'm not a programmer, so
the code I've provided is likely very inefficient. my question is as
follows. I do a great deal of "scenario" analysis. it would be
beneficial to be able to change the "strategy" and copy the relevant
results to a certain location without having to copy numerous lines of
code for each scenario I'm testing. an example is below.

I take a "strategy" called "equity" and subtract the base case from it,
subsequently storing the results. my base case "equity" is 1, all
others are triggered by the numbers 2 - 12 in this instance. my result
table is a series of columns with names that go from "Res_ED_60" to
"Res_ED_70" with only the last 2 digits changing. to help with this
making a bit more sense, the results are simply (Res)ult (E)quity
(D)etermination (60) % of equity. the results are always copied from
"Res_ED". it would seem to me that I can put this in some type of loop
where the only things changing are the "equity" scenario in line 2 of
the code, and the relevant result set name in line 22 of the code. in
the event the lines get word wrapped incorrectly, I copy/pasted the 2
lines I think are relevant immediately below.
---------------------------------
ActiveCell.FormulaR1C1 = "3"
Application.Goto Reference:="Res_ED_61"
---------------------------------

Application.Goto Reference:="equity"
ActiveCell.FormulaR1C1 = "3"
Calculate
Application.Goto Reference:="active_equity"
Selection.Copy
Application.Goto Reference:="base_equity"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="active_label"
Selection.Copy
Application.Goto Reference:="base_label"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="equity"
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "1"
' copies ED cumulative imbalance results to results table
Application.Goto Reference:="Res_ED"
Selection.Copy
Application.Goto Reference:="Res_ED_61"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Thank you again for any help on this issue. I'm hoping it's a fairly
easy task.


--
wingfield
------------------------------------------------------------------------
wingfield's Profile: http://www.excelforum.com/member.php...o&userid=12968
View this thread: http://www.excelforum.com/showthread...hreadid=536477