I am trying to input a variable as an argument in the RANDBETWEEN function.
We do random staff draws for prizes so I automated the draw against numbered
staff names then produce the name from a VLOOKUP of the relevant name beside
the number. The problem is that there are 5 draws every month and the number
of people are constantly changing so the range changes too. Rather than have
to train someone to alter the number in the RANDBETWEEN function ex:(1,365) I
want to replace the ,365 with the row count produced from the following code,
but it does not work - it produces the #NAME? error:
Private Sub CommandButton1_Click()

Dim cRows As Long


Dim RANDBETWEEN As AddIn

cRows = Cells(Rows.Count, "A").End(xlUp).Row

range("B8").Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(1,cRows)"
range("B9").Select


End Sub

Any ideas please?

Thanks

Nigel Forge