# Looping a Formula?

#### danhenshy23

##### New Member
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

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Here's one way:

Code:
``````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.

Last edited:
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?

Replies
7
Views
274
Replies
4
Views
394
Replies
2
Views
329
Replies
5
Views
343
Replies
3
Views
244

1,196,407
Messages
6,015,099
Members
441,870
Latest member
kojack

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back