Need a loop in my macro

D

DA

The following macro needs a loop and two variables (with
dimensioning). I would appreciate if anyone can insert them for me,
in place of what would no longer be needed below. In the loop (23
iterations), the macro changes one cell in increments of 0.01. Then,
it (23 times) goes to another cell and goal seeks it to a value that
also increases by 0.01. Then, it simply cuts and pastes the results
of 11 various cells into a column over to the right.

In the next iteration, it adds another 0.01 to the two things
mentioned above, and then pastes the results again over to the right
again, but, this time, by one more column over, so that, ultimately, I
have a 23 column by 11 row matrix of pasted values, in addtion to the
2 column (so my end-right, then right one more cell, operation should
work) by 11 row matrix that I start with.

Here is the macro (with my comments in UPPER CASE), please help! I
promise I will put this in a safe place so i can figure it out next
time, myself. Thanks
Dean

GoalSeekRoutine Macro

Application.Goto Reference:="SecondAssetDesignIRR".

'SET IT INITIALLY TO -0.21

ADD 0.01 TO THE VALUE PREVIOUSLY IN THIS CELL, WHICH CREATES A
RANGE FROM -0.20 TO +0.08 AS WE LOOP THRU, 23 TIMES

Application.Goto Reference:="GrossEquityIRR"
Range("GrossequityIRR").GoalSeek Goal:=0.09, ChangingCell:=Range
("FirstAssetDesignIRR")

'IN THE STATEMENT ABOVE, WHERE THE 0.09 IS, IN THE FIRST LOOP
THRU, IT SHOULD USE A VALUE OF 0.08 AND, ON EACH SUCCESSIVE LOOP THRU,
IT SHOULD ADD 0.01 TO THE VALUE PREVIOUSLY IN THIS CELL, WHICH STARTS
AT 0.08 AND ENDS AT 0.30

'THIS IS THE START OF THE REST OF THS MACRO, WHICH SIMPLY COPIES AND
PASTES.

Application.Goto Reference:="GrossequityIRR"
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="TopLeftCorner ' FYI, THIS IS CELL N6


FOR THE FIRST CELL TO BE COPIED, I WANT THE CURSOR TO GO DOWN BY ZERO
CELLS, THEN DO AN END-RIGHT, AND THEN GO RIGHT BY ONE MORE CELL, THEN
PASTE SPECIAL

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="FirstassetIRR"
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="TopLeftCorner"

Range("N7").Select

'INSTEAD OF N7 ABOVE, I WANT THE CURSOR TO GO DOWN BY 1 CELL, THEN DO
AN END-RIGHT, THEN GO RIGHT ONE MORE CELL

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="SecondAssetIRR"
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="TopLeftCorner"

Range("N8").Select

'INSTEAD OF N8 ABOVE, I WANT THE CURSOR TO GO DOWN BY 2 CELLS, THEN
DO AN END-RIGHT, THEN GO RIGHT ONE MORE CELL

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="P13CBminus"
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="TopLeftCorner"

Range("N9").Select

'INSTEAD OF N9 ABOVE, I WANT THE CURSOR TO GO DOWN BY 3 CELLS, THEN
DO AN END-RIGHT, THEN GO RIGHT ONE MORE CELL

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="P13NetIF"
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="TopLeftCorner"
Range("N10").Select

'INSTEAD OF N10 ABOVE, I WANT THE CURSOR TO GO DOWN BY 4 CELLS,
THEN DO AN END-RIGHT, THEN GO RIGHT ONE MORE CELL

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="P14WarrantedIF"
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="TopLeftCorner"
Range("N11").Select

'INSTEAD OF N11 ABOVE, I WANT THE CURSOR TO GO DOWN BY 5 CELLS, THEN
DO AN END-RIGHT, THEN GO RIGHT ONE MORE CELL

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="P14Recapture"
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="TopLeftCorner"
Range("N12").Select

'INSTEAD OF N12 ABOVE, I WANT THE CURSOR TO GO DOWN BY 6 CELLS, THEN
DO AN END-RIGHT, THEN GO RIGHT ONE MORE CELL

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="P13PercentFeeLoss"
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="TopLeftCorner"
Range("N13").Select

'INSTEAD OF N13 ABOVE, I WANT THE CURSOR TO GO DOWN BY 7 CELLS, THEN
DO AN END-RIGHT, THEN GO RIGHT ONE MORE CELL

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="P13PercentFeeLoss"
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="TopLeftCorner"
Range("N14").Select

'INSTEAD OF N14 ABOVE, I WANT THE CURSOR TO GO DOWN BY 8 CELLS, THEN
DO AN END-RIGHT, THEN GO RIGHT ONE MORE CELL

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="TruePortfolioPercentFeeLoss"
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="TopLeftCorner"
Range("N15").Select

'INSTEAD OF N15 ABOVE, I WANT THE CURSOR TO GO DOWN BY 9 CELLS, THEN
DO AN END-RIGHT, THEN GO RIGHT ONE MORE CELL

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="P14NetActualIF"
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="TopLeftCorner"
Range("N16").Select

'INSTEAD OF N16 ABOVE, I WANT THE CURSOR TO GO DOWN BY 10 CELLS,
THEN DO AN END-RIGHT, THEN GO RIGHT ONE MORE CELL

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
 
J

Joel

Try this

Sub GoalSeekRoutine()

'set Destination column to N
DestCol = Range("TopLeftCorner").Column
DestRow = Range("TopLeftCorner").Row
For SecondAsset = -0.2 To 0.08 Step 0.01
Range("SecondAssetDesignIRR") = SecondAsset
DestRow = Range("TopLeftCorner").Row
For FirstAsset = 0.08 To 0.3 Step 0.01
Range("GrossequityIRR").GoalSeek _
Goal:=FirstAsset, _
ChangingCell:=Range("FirstAssetDesignIRR")
Range("GrossequityIRR").Copy
Cells(DestRow, DestCol).PasteSpecial _
Paste:=xlPasteValues
DestRow = DestRow + 1
Next FirstAsset
DestCol = DestCol + 1
Next SecondAsset

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy paste - Values - formula - Optimise 2
Help merging two VBA codes 2
Having difficulty copying and pasting on VBA 3
range copy and paste special within macro 2
cannot insert row in vba 1
lenghty code 4
Text String (Edit w/Carriage Return) 3
Copying Solver results of each iteration to a given location in a workbook 1

Top