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

Thanks! I think I just about have educated myself on what this code is
doing. I noticed that since my rows exceed 300 it takes 10-11 seconds for
each iteration of the macro to run. If I want to do 100 or 1000 runs this is
a long time. So, I tried eliminating the iCT2 FOR/NEXT code and left my A
column with RAND() in each of the 300+ cells. The macro now executes in just
a second or so. I want to be sure that this modification is legitimate and I
am not overlooking something... I almost have it doing what I want. Thanks
for the tip on Visual help...that was very useful to understand the Visual
code!

Now just one question remains and I think I will be able to finish it up.
In the D column I used an array formula to determine the quantity of the
number 2 through 5:
{=sum(if((d2:indirect("D"&E300-1)<6),1,0))-sum(if((d2:indirect("D"&E300-1)<2),1,0))}
Perhaps cumbersome for you experts, but it works for me. I tried to figure
out another array formula to compute the percentage of occurances in two
seperated columns. In cell AD301 I use a =countif(ad2:ad300,1) to determine
the total number of times an event occurs. Now I need to count the number of
times when there is a 1 in cell ADxxx there is a 1 in the corresponding cell
ABxxx. Thus, I can get my percentage of ABxxx/ADxxx. When I try
TOOLS/Formula Auditing/Evaluate something shows up as 1;1;1 multiple times
since I defined a range of AB2:AD300. I took a few guesses at an array
formula but can't seem to get it right.

Any additional help would be most appreciated....you two have been terrific!!!

Tam, (Thanks A Million)
Craig

"Tom Ogilvy" wrote:

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