PDA

View Full Version : Looping a solving procedure?



ermis123
01-20-2011, 07:38 AM
I have a dataset where i need to solve for a specific variable.

The dataset consists of 7 columns of data and 1000 rows (7 datatypes and1000 observations) located in the sheet "Data". I have constructed a second sheet named "Solver" where i want to run all 1000 observations and solve the function through goal seek and then paste the result into a third sheet "Output".

I am however totally new to VBA programming and need some help. Have read for a few hours but not figured it out yet. Below you find a recorded macro of the procedure I want to achieve. This only performs the procedure for one row and paste one result in the "Output" sheet.

I would appreciate some help on how to advance from here and calculate all 1000 observations in a simple click. To be more precise - what I want to achieve (code in the end),

1. Select row 2 in "Data".
2. Copy this to row 6 in" solver" sheet.
3. Solve (these cells are fixed)
4. Copy value to row 1 in the "output" sheet.
5. Repeat the process for row 3 in "Data".
6. Copy this to row 6 in "solver" sheet (row 6 is fixed).
7. Solve.
8. Copy value to row 2 in the "output" sheet.

Thanks in advance.

Sub Final()
' Final Macro
' Sheets("Data").Select
Rows("2:2").Select
Selection.Copy
Sheets("Solver").Select
Rows("6:6").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("J10").GoalSeek Goal:=0, ChangingCell:=Range("B11")
Range("B11").Select
Selection.Copy
Sheets("Output").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

shrivallabha
01-20-2011, 09:04 AM
First, you can tidy up the recorded code. This will make it better and easier on your eyes.
Tip#1: Get rid of select / selection
Change this part to
Sheets("Data").Select
Rows("2:2").Select
Selection.Copy

as:
Sheets("Data").Rows("2:2").Copy

2nd part:
Sheets("Solver").Select
Rows("6:6").Select
ActiveSheet.Paste
Application.CutCopyMode = False

To:
ActiveSheet.Paste Destination:=Sheets("Solver").Rows("6:6")
Application.CutCopyMode = False


Now For creating a loop, For Next Loop will suit you best.

Dim LastRow As Long
'Finding last row of the data.
LastRow = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
'Your updated code then will be placed here.
Next i


Hth.

ermis123
01-20-2011, 11:55 AM
Thank you for your fast reply, it did however not behave as hoped. My code looks like this now.

Sub Makro()
' Makro
Dim LastRow As Long
'Finding last row of the data.
LastRow = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
Sheets("Data").Rows("2:2").Copy
ActiveSheet.Paste Destination:=Sheets("Solver").Rows("6:6")
Application.CutCopyMode = False
Sheets("Solver").Select
Range("J10").GoalSeek Goal:=0, ChangingCell:=Range("B11")
Sheets("Solver").Rows("11:11").Copy
ActiveSheet.Paste Destination:=Sheets("Output").Rows("1:1")
Application.CutCopyMode = False
Next i
End Sub


This code produce one value in the "Output" sheet and dot not repeat any calculations for the following variables (row 3, row 4 etc etc). After i run the macro, the values in the "Solver" sheet is from row 2 in the "Data" sheet. Thus, the code do not include all rows in the "data sheet"

Do this code also "count" the rows in the "Output" sheet? For every new solved equation a new output row must be used.

I would be extremely greatful for more support on this issue.

Thanks.

mancubus
01-20-2011, 02:04 PM
your code does not increment i.

try


Sub Makro()
'
' Makro

Dim LastRow As Long, LR As Long

'Finding last row of the data.
LastRow = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow
Sheets("Data").Rows(i).Copy Destination:=Sheets("Solver").Rows("6")

Sheets("Solver").Select
Range("J10").GoalSeek Goal:=0, ChangingCell:=Sheets("Solver").Range("B11")

LR = Sheets("Output").Range("A65536").End(3).Row + 1
Sheets("Solver").Rows("6").Copy Destination:=Sheets("Output").Rows(LR)
Next

End Sub

ermis123
01-20-2011, 03:16 PM
The macro works PERFECT! Thank you for your invaluable inputs. You saved my day. I bet this little macro will come in handy in many future occasions.

Thanks!

shrivallabha
01-20-2011, 10:11 PM
Mancubus has already supplied you with the code. Please see that he has used a variable for the changing row (i) from the worksheet data.

The second variable he has inserted LR is for finding the next row in the output sheet to avoid overwriting. If you compare LastRow and LR then they are different ways of writing the same syntax.