Looping a Formula?

[danhenshy23]

Hi,

I am very new to VBA, so apologies if this is a very simple query.

I am creating a random name generator in Excel and want to achieve an effect where it appears that the generator is cycling through the list of names.

To do this I want to create a macro which will run my formula 50 times when the "Run" button is pressed.

So far I have the following entered into a module, which is working. How do I now go about adding the loop?


Sub Enter_Formulas()
Range("D4").Formula = "=ROUND((RAND()*'List'!C4)+0.5,0)"


End Sub



Thanks
Dan

 

[Asala42]

Here's one way:

Dim i as long

Range("D4").Formula = "=ROUND((RAND()*'List'!C4)+0.5,0)"

For i = 1 to 50
Calculate
'Put results in a nearby range
Cells(i,20).value = Range("D4").value

next i

The formula only needs to be entered once. The loop recalculates, which with your use of rand() should create another random entry, then toss the 50 results in column T (column 20). Range T1:T50 should populate your list of random entries.

 

[danhenshy23]

Sorry I should have been clearer, I'm wanting the macro to have the formula run 50 times to give the impression that or is scrolling through the names, rather than return 50 names in a range.

Can this be achieved?