Looping along the top row.

[simmantix]

I wish to use VBA to insert a column of random numbers into a spreadsheet using the Data Analysis Add in - Random Number Generator.

My problem is that the macro for the random number generation requires the cell refernce in which to start the column of numbers.

Syntax:
Application.Run "ATPVBAEN.XLA!Random", ActiveSheet.Range(RefCell), 1, 200, 2, 1, 2.83, 0.5

The 'RefCell' is the problem, having assigned 'RefCell' with the required cell designation using the following:

Set RefCell = Worksheets("Sheet3&quot.Cells(2, Counter)

The generator will not work using Range (refcell) so can anyone tell me how to get in to work.

In the end I would like the spreadsheet code to loop along the horizontal axis, top row of the spreadsheet filling in columns of rabndom numbers as it goes with code something like this:

Counter = 1
Do Until Counter = 20
Set RefCell = Worksheets("Sheet3&quot.Cells(2, Counter)
Application.Run "ATPVBAEN.XLA!Random", ActiveSheet.Range(RefCell), 1, 200, 2, 1, 2.83, 0.5

Counter = Counter + 1
Loop

so I should end up with 20 seperate columns of random numbers using a loop or something..

Anyone??

Help would be greatly appreciated..

S

 

[joerd]

It is almost fine, but for one mistake: once you set RefCell to point to the range, there is no need for the ActiveSheet.Range (which expects a string type cell reference).

So your code should be:

Counter = 1
Do Until Counter = 20
   Set RefCell = Worksheets("Sheet3").Cells(2, Counter)
   Application.Run "ATPVBAEN.XLA!Random", RefCell, 1, 200, 2, 1, 2.83, 0.5
   Counter = Counter + 1
Loop

Regards,

Joerd

 

[simmantix]

Thankyou very much.. I was trying to solve that one all last night..

I there a place where all the syntax's for all the macro's ciommands etc in Excel reside??

S