I want to run some trial runs and store the results from each run. Here is a
simple example to see if it can be done.
A B C
D
1 =RAND() 1 =B1+1 =B1+2
2 =RAND() 2 =B2+2 =B2+7
3 =RAND() 3 =B3*2 =B3+4
4 =RAND() 4 =B4/B1 =B4+B2
5 "Results" =SUM(C1:C4) =SUM(D1:D4)

11 "Result 1" w x
12 "Result 2" y
z
13 etc...

50 "Averages" =AVG(C11:C49) =AVG(D11:d49)



My sort macro acts on columns A and B and reorganizes the B column data each
time I run it. I then need to copy/paste the results in the result area for
each run. I am looking for a way to automate the macro to run x times and
place the results in the correct result line (needs to be incremented each
run). I do not know how to program in Visual and was hoping it could be done
with basic Excel functions.

Thanks,

Craig

Something like this calls for VBA.

Sub Macro1()
Dim iCt As Integer
Dim iCt2 As Integer
Dim iMax As Integer
Dim ws As Worksheet

Set ws = Sheets("Sheet1")
iMax = InputBox("Enter number of iterations.")

ws.Range("A11:D500").Clear
For iCt = 1 To iMax
For iCt2 = 1 To 4
ws.Cells(iCt2, 1) = Rnd
Next iCt2
ws.Range("A1:B4").Sort Key1:=Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ws.Cells(10 + iCt, 1) = "Result " & iCt
ws.Cells(10 + iCt, 3) = ws.Cells(5, 3)
ws.Cells(10 + iCt, 4) = ws.Cells(5, 4)
Next iCt
ws.Cells(10 + iMax + 1, 1) = "Averages"
ws.Range("C" & 10 + iMax + 1 & ":D" & 10 + iMax + 1) _
.FormulaR1C1 = "=AVERAGE(R[" & -iMax & "]C:R[-1]C)"

End Sub


Hth,
Merjet

I appreciate you taking the time to generate the code. Unfortunately, I am
not a VBA programmer so I really don't know what to do with it. I was hoping
I could automate the process with standard Excel functions. I appreciate
your efforts! I think I am doomed to just manually run my macro and
copy/paste special/values into the result area. A bit tedious for a 50 or
100 set run, but then again ....

Thank you!!

"merjet" wrote:

Something like this calls for VBA.

Sub Macro1()
Dim iCt As Integer
Dim iCt2 As Integer
Dim iMax As Integer
Dim ws As Worksheet

Set ws = Sheets("Sheet1")
iMax = InputBox("Enter number of iterations.")

ws.Range("A11:D500").Clear
For iCt = 1 To iMax
For iCt2 = 1 To 4
ws.Cells(iCt2, 1) = Rnd
Next iCt2
ws.Range("A1:B4").Sort Key1:=Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ws.Cells(10 + iCt, 1) = "Result " & iCt
ws.Cells(10 + iCt, 3) = ws.Cells(5, 3)
ws.Cells(10 + iCt, 4) = ws.Cells(5, 4)
Next iCt
ws.Cells(10 + iMax + 1, 1) = "Averages"
ws.Range("C" & 10 + iMax + 1 & ":D" & 10 + iMax + 1) _
.FormulaR1C1 = "=AVERAGE(R[" & -iMax & "]C:R[-1]C)"

End Sub


Hth,
Merjet

Select the Excel menu Tools | Macro | Visual Basic Editor. Select the
Editor menu Insert | Module. Paste my VBA code in Module1. Close the
Editor. Select the Excel menu Macro | Macros. In the popup window
select Macro1. That will execute the VBA code.

Hth,
Merjet

I tried your code on the sample I posted and it was cool!! Now I need to
study it so I can figure out how to modify it to work for my real data - a
much more complicated spreadsheet than my example. Is there any Visual Basic
help within Excel or another place? And about your sign off Hth - what does
that mean?

"merjet" wrote:

Select the Excel menu Tools | Macro | Visual Basic Editor. Select the
Editor menu Insert | Module. Paste my VBA code in Module1. Close the
Editor. Select the Excel menu Macro | Macros. In the popup window
select Macro1. That will execute the VBA code.

Hth,
Merjet

HTH means "hope that helps"

In the visual basic editor there is help on VBA.

--
regards,
Tom Ogilvy

"Craig" wrote in message
...
I tried your code on the sample I posted and it was cool!! Now I need to
study it so I can figure out how to modify it to work for my real data - a
much more complicated spreadsheet than my example. Is there any Visual
Basic
help within Excel or another place? And about your sign off Hth - what
does
that mean?

"merjet" wrote:

Select the Excel menu Tools | Macro | Visual Basic Editor. Select the
Editor menu Insert | Module. Paste my VBA code in Module1. Close the
Editor. Select the Excel menu Macro | Macros. In the popup window
select Macro1. That will execute the VBA code.

Hth,
Merjet