Excel VB
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(1," & cRows & ")"
"Nigel Forge" wrote in message
...
: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
|