Help with Random Numbers 1

[aburamewolf]

I would really appreciate some help right now, it's my first attempt at working with random numbers in VBA.

I'm working in a "raffle" program who's end result should be employees inputting their employee number, which in turn will trigger a look-up for their name and hence a row number can be obtained.

In that row there will be pre-assigned categories for that agent to fall under such as: ladies, guys, top performers, seniority, etc...

So once I have randomize a category to be selected, I would need the macro to go to the "Prize" sheet and according to the category chosen select a range which will contain a list of prizes that are eligible for that category only.

I found a way to generate that random number using

LRandomNumber = Int((3 - 1 + 1) * Rnd + 1)to generate a limit to the random numbers that can be generated (I've tested it by throwing the input into a textbox to see the number output and it is working) but I can't really find a way to make good usage of that number (I'm just brain frozen right now)

so right now the only functional part of code is this:

Private Sub UserForm_activate()
Dim xvalue
Dim xrow As Long


xvalue = start1.anumber.Value

If xvalue <> "" Then
Sheets("agents").Select
xrow = Cells.Find(what:=xvalue, lookat:=xlWhole).Row
name1.Text = Cells(xrow, 2)

End If
End Sub

the categories would be stored columns C to F
(if a category has been left blank I would like it for that number to be re-randomized)

Any suggestion will be greatly appreciated!

 

[aburamewolf]

I have figured a way to make use of the data by I'm having trouble making use of it later on, I guess I would need to declare it as a public variable but up until now I have only worked with private subs I declared it as public in a module but that didn't work, any suggestions?

this is what I have so far:

Private Sub userform_activate()
Dim xvalue
Dim xrow As Long
Dim LRandomNumber As Integer
Dim xrange As Range
Dim group1 As Variant

xvalue = start1.anumber.Value

If xvalue <> "" Then
Sheets("agents").Select
xrow = Cells.Find(what:=xvalue, lookat:=xlWhole).Row
name1.Text = Cells(xrow, 2)

Do
    LRandomNumber = Int((4 - 1 + 1) * Rnd + 1)
    If LRandomNumber = 1 Then
        group1 = Cells(xrow, 3)
    ElseIf LRandomNumber = 2 Then
        group1 = Cells(xrow, 4)
    ElseIf LRandomNumber = 3 Then
        group1 = Cells(xrow, 5)
    ElseIf LRandomNumber = 4 Then
        group1 = Cells(xrow, 6)
    End If
    Loop Until group1 <> ""
End If

End Sub

 

[Bong]

Put this: Dim LRandomNumber As Integer
outside (i.e., before) the "Sub"

_________________
Bob Rashkin

 

[aburamewolf]

Thanks Bob!