Bernie,
Thanks for the reply, I don't have any other macros, events or commands on
the worksheet.
What I am trying to do is the following:
1) Count the number of occurrences with the same start date and store them
in a variable
2) Take variable and pass it to random function to output random number
within range of variable (ie:1-8)
3) Output Random number to Lottery Spreadsheet within the scope of the
variable i.e.: insert column and output number
Does not sound difficult, but I am having a hard time getting it done based
upon the limited help in Excel
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Jim,
As far as I can see, your code doesn't do anything: do you have other
macros
or events or commandbuttons in your workbook?
Also, code like
With Worksheets("Lottery").Column(5).Add = RandomNumber
is simply..... - I just don't know how to describe it. Worksheets don't
have
a .Column property (they do have a .Columns property), and there is no Add
method for .Columns.
What is it that you are trying to do?
HTH,
Bernie
MS Excel MVP
"Jim Campbell" wrote in message
...
I am trying to use the Random Function in VB to generate a series of
Random
numbers equal to a counter that is date based. and insert the random
numbers
into a cell on the same spreadsheet. I am having some problems getting
it
to
work, any help would be appreciated as I am not a VB programmer. The
Code
is
below:
Dim StartDate As Date ' Advisor Start Date
Dim GroupCount As Integer ' Counter for determining how many
advisors with same start date
Dim RandomNumber As Integer ' Random number value generated by
Randomize
function
'
' Macro recorded 2004-05-14 by Jim Campbell
' This Macro was created to generate random numbers to be used
' in determining the order of choice based upon the same date
' within a group
'
'
' Specify Worksheet and column to work with
Sub Column_Select()
Worksheets("Lottery").Columns (4) ' Start date of Advisor
End Sub
' Check_Start_Date Format (DD-MON-YYYY) will determine number
' to be used for next module that generates Random Numbers.
' GroupCount determines how many random numbers are generated
' based upon Start Date
'
'
Sub Check_Start_Date()
Check = True: GroupCount = 0 ' Initialize variables.
Do ' Outer loop.
Do While StartDate = StartDate ' Inner loop.
GroupCount = GroupCount + 1 ' Increment Counter.
If StartDate < StartDate Then 'If condition is True.
Check = False ' Set value of flag to False.
Exit Do ' Exit inner loop.
End If
Loop
Loop Until Check = False ' Exit outer loop immediately
End Sub
' Use GroupCount value as input for
' module that generates Random Numbers
'
'
Sub Random_Number()
Randomize ' Initialize random-number generator.
RandomNumber = Int((GroupCount * Rnd) + 1) ' Generate random
number
based on GroupCount
End Sub
'
' If cell is is not empty, add value to worksheet
' Add column with Random Number value to Lottery.xls
'
'
Sub Add_Column()
For Each c In Worksheets("Lottery").Range(RandomNumber).Cells '
Specify
cells that will be populated with number
If c.Value < Null Then
With Worksheets("Lottery").Column(5).Add = RandomNumber ' Add
random
number to Lottery.xls(column 5)
End With
End If
Next c
End Sub
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Jim,
As far as I can see, your code doesn't do anything: do you have other
macros
or events or commandbuttons in your workbook?
Also, code like
With Worksheets("Lottery").Column(5).Add = RandomNumber
is simply..... - I just don't know how to describe it. Worksheets don't
have
a .Column property (they do have a .Columns property), and there is no Add
method for .Columns.
What is it that you are trying to do?
HTH,
Bernie
MS Excel MVP
"Jim Campbell" wrote in message
...
I am trying to use the Random Function in VB to generate a series of
Random
numbers equal to a counter that is date based. and insert the random
numbers
into a cell on the same spreadsheet. I am having some problems getting
it
to
work, any help would be appreciated as I am not a VB programmer. The
Code
is
below:
Dim StartDate As Date ' Advisor Start Date
Dim GroupCount As Integer ' Counter for determining how many
advisors with same start date
Dim RandomNumber As Integer ' Random number value generated by
Randomize
function
'
' Macro recorded 2004-05-14 by Jim Campbell
' This Macro was created to generate random numbers to be used
' in determining the order of choice based upon the same date
' within a group
'
'
' Specify Worksheet and column to work with
Sub Column_Select()
Worksheets("Lottery").Columns (4) ' Start date of Advisor
End Sub
' Check_Start_Date Format (DD-MON-YYYY) will determine number
' to be used for next module that generates Random Numbers.
' GroupCount determines how many random numbers are generated
' based upon Start Date
'
'
Sub Check_Start_Date()
Check = True: GroupCount = 0 ' Initialize variables.
Do ' Outer loop.
Do While StartDate = StartDate ' Inner loop.
GroupCount = GroupCount + 1 ' Increment Counter.
If StartDate < StartDate Then 'If condition is True.
Check = False ' Set value of flag to False.
Exit Do ' Exit inner loop.
End If
Loop
Loop Until Check = False ' Exit outer loop immediately
End Sub
' Use GroupCount value as input for
' module that generates Random Numbers
'
'
Sub Random_Number()
Randomize ' Initialize random-number generator.
RandomNumber = Int((GroupCount * Rnd) + 1) ' Generate random
number
based on GroupCount
End Sub
'
' If cell is is not empty, add value to worksheet
' Add column with Random Number value to Lottery.xls
'
'
Sub Add_Column()
For Each c In Worksheets("Lottery").Range(RandomNumber).Cells '
Specify
cells that will be populated with number
If c.Value < Null Then
With Worksheets("Lottery").Column(5).Add = RandomNumber ' Add
random
number to Lottery.xls(column 5)
End With
End If
Next c
End Sub