Excel VB

[Nigel Forge]

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 ex1,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

 

[PaulD]

ActiveCell.FormulaR1C1 = "=RANDBETWEEN(1," & cRows & ")"

: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
ex1,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

 

[Don Guillett]

Sub makeformula()'NO selections
cRows = Cells(Rows.Count, "A").End(xlUp).Row
Range("b8").Formula = "=RANDBETWEEN(1," & cRows & ")"
End Sub

 

[Nigel Forge]

Thanks Paul! My kingdom for an ampersand (or two)!

 

[Nigel Forge]

Thanks Don! Much appreciated.

Nigel

Sub makeformula()'NO selections
cRows = Cells(Rows.Count, "A").End(xlUp).Row
Range("b8").Formula = "=RANDBETWEEN(1," & cRows & ")"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Nigel Forge]

OK , so when I tried it failed as it always selects the number 1.
I found a solution as follows:
Range("B8")= "=RANDBETWEEN (1, COUNT (Data!A1:A5000))"

This selects the whole range of numbers fine. Wonder why the cRows solution
didn't work though?

Thanks