Excel VB
 

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

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

Excel VB
 

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

"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

Excel VB
 

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

"PaulD" wrote:

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

Excel VB
 

Thanks Don! Much appreciated.

Nigel

"Don Guillett" wrote:

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

"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

Excel VB
 

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

"Don Guillett" wrote:

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

"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