Excel Macro/Script Problems

[Excelhelp]

I have been working on this for several days now and despirately need help. I need a drop down list box numbering from 1 to 10 next to a command button. The command button should run the following script for the number of times as selected by the list box:

Private Sub CommandButton1_Click()
Range("A5").Select
ActiveCell.FormulaR1C1 = "=RAND()*(1-20)+20"
Range("A5").Select
End Sub

The script generates a random number from 1 to 20, but I need it to generate the number of selected by the list box, in cells underneath each other, ie. A5, A6, A7, A8, etc.

I then have the following formula =COUNTIF(A5:A8, "<=3"), to tell me how many numbers are equal to or less than 3, but I need this connected to the command button.

Finally, I have a text box, which I need the output of those cells directed to, so that each cell is separated by a comma. I can link the text box to one cell, but cant get the output from them all.

I have started and have tried my best to get it started by I have hit a wall. If someone could please help...

[Epidemic]

http://home.mchsi.com/~lvahle1470/counternomacro.xls

Here is one with out a macro.

It might give you a clue as to how to do it with a macro.

count if is a wierd function and the way i formatted it might be the answer to your problem.

[shahani]

Good counter, Epidemic.

[Excelhelp]

Thanks for your reply Epidemic, it helped alot.

One final thing someone maybe able to help with it the link to a textbox. I need the output from the cells to be separated by a comma, listed on two separate lines.

[shahani]

Senior Member Epidemic should change name to Excelhelp.

He knows a lot of stuff about excel.

[Epidemic]

Not sure what you are asking.

"the link to a textbox. I need the output from the cells to be separated by a comma, listed on two separate lines"

Cells in a text box. the output from the text box is 1 value or are you doing something else fancy

[Excelhelp]

Ok, what I am trying to achieve is, using your spreadsheet as an example. If for instance you select 5 random numbers to be generated from the list. Is it possible to have these numbers the placed in a textbox on two separate lines, ie.
1, 2, 3
4, 5

[Epidemic]

So you are talking about putting the result in a text box?

There are several ways to do that.
Where ever the random data is let say column "D"
(in cell F1 use the following formula) =D1&","&D2","D3
(In Cell F2 "ditto" ) = D4","D5","D6

Now reference you new text or list box to F1,F2 range.

Side note: To use the data you will need to use an index formula at some point as the list box only puts out the position in an array.