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
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