I'm helping to run a raffle for our Rotary group. They've sold 30,000
tickets and are having a big posh evening to draw the prizes. As I had
a laptop and a projector, I was volunteered to knock up a display
program for the night.

I have a workbook with all the groups, and their ticket allocations on
sheet1. I also have the makings of a rudimentary random number
generator which I'll beat into shape fairly quickly. But, the project
as described to me makes it necessary to display the winning
organisations in a 2nd textbox below the winnign number.

For example, if sheet1 contains

Hospice 21101 21500 400
Youth football 21501 21650 150

And the random number generator throws up 21123, I want textbox2.value
to equal $A$1. Now, in the example below, if I insert $a$1 manually,
it does, but if I try and introduce a variable (which I'll write a
quick algorithm for later), in our example R=1 for tickets
21101-21500, and 2 for higher values. $a$R just throws up errors. This
HAS to be so simple it's stupid, yet I've been here trying for 3
hours. .... i DID say I was new to this! (< brackets are my additions
for this post)


Private Sub CommandButton1_Click()

Dim R As Integer: R = ActiveCell.Row

X = Int(Rnd() * 30000) + 10000 <first ticket numbered 10000, dont
panic
TextBox1.Value = X

R = 20

TextBox2.Value = Sheet1.[$A$R]

End Sub
<et voila, type mismatch error

try this

TextBox2.Value = Sheets("Sheet1").Cells(1, R)

Regards,
KL


"Paul Crabb" wrote in message
om...
I'm helping to run a raffle for our Rotary group. They've sold 30,000
tickets and are having a big posh evening to draw the prizes. As I had
a laptop and a projector, I was volunteered to knock up a display
program for the night.

I have a workbook with all the groups, and their ticket allocations on
sheet1. I also have the makings of a rudimentary random number
generator which I'll beat into shape fairly quickly. But, the project
as described to me makes it necessary to display the winning
organisations in a 2nd textbox below the winnign number.

For example, if sheet1 contains

Hospice 21101 21500 400
Youth football 21501 21650 150

And the random number generator throws up 21123, I want textbox2.value
to equal $A$1. Now, in the example below, if I insert $a$1 manually,
it does, but if I try and introduce a variable (which I'll write a
quick algorithm for later), in our example R=1 for tickets
21101-21500, and 2 for higher values. $a$R just throws up errors. This
HAS to be so simple it's stupid, yet I've been here trying for 3
hours. .... i DID say I was new to this! (< brackets are my additions
for this post)


Private Sub CommandButton1_Click()

Dim R As Integer: R = ActiveCell.Row

X = Int(Rnd() * 30000) + 10000 <first ticket numbered 10000, dont
panic
TextBox1.Value = X

R = 20

TextBox2.Value = Sheet1.[$A$R]

End Sub
<et voila, type mismatch error

Also if you need to precede your Rnd() function with the Randomize
statement:

Private Sub CommandButton1_Click()
Dim R As Integer: R = ActiveCell.Row
Randomize ' Initialize random-number generator.
'first ticket numbered 10000, dont panic
X = Int(Rnd() * 30000) + 10000
TextBox1.Value = X
R = 20
TextBox2.Value = Sheets("Sheet1").Cells(1, R)
End Sub

Regards,
KL


"KL" wrote in message
...
try this

TextBox2.Value = Sheets("Sheet1").Cells(1, R)

Regards,
KL


"Paul Crabb" wrote in message
om...
I'm helping to run a raffle for our Rotary group. They've sold 30,000
tickets and are having a big posh evening to draw the prizes. As I had
a laptop and a projector, I was volunteered to knock up a display
program for the night.

I have a workbook with all the groups, and their ticket allocations on
sheet1. I also have the makings of a rudimentary random number
generator which I'll beat into shape fairly quickly. But, the project
as described to me makes it necessary to display the winning
organisations in a 2nd textbox below the winnign number.

For example, if sheet1 contains

Hospice 21101 21500 400
Youth football 21501 21650 150

And the random number generator throws up 21123, I want textbox2.value
to equal $A$1. Now, in the example below, if I insert $a$1 manually,
it does, but if I try and introduce a variable (which I'll write a
quick algorithm for later), in our example R=1 for tickets
21101-21500, and 2 for higher values. $a$R just throws up errors. This
HAS to be so simple it's stupid, yet I've been here trying for 3
hours. .... i DID say I was new to this! (< brackets are my additions
for this post)


Private Sub CommandButton1_Click()

Dim R As Integer: R = ActiveCell.Row

X = Int(Rnd() * 30000) + 10000 <first ticket numbered 10000, dont
panic
TextBox1.Value = X

R = 20

TextBox2.Value = Sheet1.[$A$R]

End Sub
<et voila, type mismatch error

Thanks for your time KL, I knew it would be simple. Much appreciated.

Paul


"KL" wrote in message ...
Also if you need to precede your Rnd() function with the Randomize
statement:

Private Sub CommandButton1_Click()
Dim R As Integer: R = ActiveCell.Row
Randomize ' Initialize random-number generator.
'first ticket numbered 10000, dont panic
X = Int(Rnd() * 30000) + 10000
TextBox1.Value = X
R = 20
TextBox2.Value = Sheets("Sheet1").Cells(1, R)
End Sub

Regards,
KL