Macro automatic execution x times

[Guest]

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(D14)

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

 

[merjet]

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("A11500").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 & "" & 10 + iMax + 1) _
.FormulaR1C1 = "=AVERAGE(R[" & -iMax & "]C:R[-1]C)"

End Sub


Hth,
Merjet

 

[Guest]

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]

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

 

[Guest]

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?

 

[Tom Ogilvy]

HTH means "hope that helps"

In the visual basic editor there is help on VBA.

 

[Guest]

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