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

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,

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

Jim,

See my questions in-line.

HTH,
Bernie
MS Excel MVP

"Jim Campbell" wrote in message
...
1) Count the number of occurrences with the same start date and store them
in a variable

Where is the start date stored? Which cells do you want to compare to the
start date? (Give sheet name and cell addresses)

2) Take variable and pass it to random function to output random number
within range of variable (ie:1-8)

Do you mean you want a random number between 1 and the count from step 1?
Where do you want the random number put?

3) Output Random number to Lottery Spreadsheet within the scope of the
variable i.e.: insert column and output number

A new column inserted everytime? What should the new column have in it?

Does not sound difficult

It shouldn't be hard, but you need to be more precise in your descriptions:
"I want to read the date in cell A1 of "Sheet1", and compare it to cells
B1:B10 of "Sheet2" and then.....

Bernie,
1)Date is stored in Lottery .xls in column 4 , want to get number with same
date and store in GroupCount(see below)
if the date is different, then exit and go to step 2

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

2) Yes, the value from GroupCount will be used to generate the Random number
, and within the value put out the random number to column 5 in the
Lottery.xls spreadsheet i.e.: GroupCount is 8, put out value from 1-8 in
column 5 all within the same date range(GroupCount with the same date)

3) I don't know if it is more efficient to output random number within step
2 and a new value will be put out every time within the date range criteria,
the new column will have the Random number generated from the function

Hope I made this clear, your input is appreciated
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Jim,

See my questions in-line.

HTH,
Bernie
MS Excel MVP

"Jim Campbell" wrote in message
...
1) Count the number of occurrences with the same start date and store
them
in a variable

Where is the start date stored? Which cells do you want to compare to the
start date? (Give sheet name and cell addresses)

2) Take variable and pass it to random function to output random number
within range of variable (ie:1-8)

Do you mean you want a random number between 1 and the count from step 1?
Where do you want the random number put?

3) Output Random number to Lottery Spreadsheet within the scope of the
variable i.e.: insert column and output number

A new column inserted everytime? What should the new column have in it?

Does not sound difficult

It shouldn't be hard, but you need to be more precise in your
descriptions:
"I want to read the date in cell A1 of "Sheet1", and compare it to cells
B1:B10 of "Sheet2" and then.....

Jim,

1) Tell me if I'm getting warm. You want to count the number of occurences
of a date in column D, and put random numbers next to those dates in column
E.

Now, you don't say where the master StartDate is stored, the date that all
the other dates are compared to.

For the example code, let's say that the master start date is stored in cell
A1.

Instead of your procedure Check_Start_Date, the single line

GroupCount = Application.CountIf(Range("D:D"), Range("A1"))

will count occurences of that date in column D of the activesheet.

That said, try running the first macro below (but copy both the sub and the
function into the same codemodule - the sub uses the function to create the
random numbers). When run, the sub will put random numbers next to the dates
in column D that match the date value in cell A1.

If you need help modifying the code beyond that, post again.

HTH,
Bernie
MS Excel MVP

Sub JimsRandomNumbers()
Dim myRand As Variant
Dim myCnt As Integer
Dim i As Integer
Dim myCell As Range

myCnt = Application.CountIf(Range("D:D"), Range("A1"))
myRand = UniqueRands(1, myCnt, myCnt)

With Range("D:D")
Set myCell = .Find(Range("A1").Value)
myCell(1, 2).Value = myRand(LBound(myRand))

For i = 2 To myCnt
Set myCell = .FindNext(myCell)
myCell(1, 2).Value = myRand(i)
Next i
End With
End Sub

Function UniqueRands(ByVal MinNum As Long, _
ByVal MaxNum As Long, _
ByVal NumResults As Long) As Variant

Dim NumArr() As Long
Dim ResArr() As Long
Dim ResNdx As Long
Dim GetNdx As Long
Dim GetCounter As Long
Dim TempCounter As Long
Dim myTemp As Variant
Dim i As Integer
Dim j As Integer

If MinNum = MaxNum Then
UniqueRands = Null
Exit Function
End If
If NumResults Abs(MaxNum - MinNum + 1) Then
UniqueRands = Null
Exit Function
End If

ReDim NumArr(1 To Abs(MaxNum - MinNum + 1))
ReDim ResArr(1 To NumResults)

For ResNdx = 1 To UBound(NumArr)
NumArr(ResNdx) = MinNum + ResNdx - 1
Next ResNdx

For ResNdx = 1 To NumResults
GetCounter = Int((UBound(NumArr) * Rnd) + 1)
GetNdx = 1
TempCounter = 0
Do Until TempCounter = GetCounter
If GetNdx = UBound(NumArr) Then
GetNdx = 1
Else
GetNdx = GetNdx + 1
End If

If NumArr(GetNdx) = MinNum Then
TempCounter = TempCounter + 1
End If
Loop
ResArr(ResNdx) = NumArr(GetNdx)
NumArr(GetNdx) = MinNum - 1
Next ResNdx

UniqueRands = ResArr

End Function

"Jim Campbell" wrote in message
...
Bernie,
1)Date is stored in Lottery .xls in column 4 , want to get number with
same
date and store in GroupCount(see below)
if the date is different, then exit and go to step 2

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

2) Yes, the value from GroupCount will be used to generate the Random
number
, and within the value put out the random number to column 5 in the
Lottery.xls spreadsheet i.e.: GroupCount is 8, put out value from 1-8 in
column 5 all within the same date range(GroupCount with the same date)

3) I don't know if it is more efficient to output random number within
step
2 and a new value will be put out every time within the date range
criteria,
the new column will have the Random number generated from the function

Hope I made this clear, your input is appreciated

The date is stored in column 4 of the Lottery.xls spreadsheet, and the
random number based upon the range of numbers(i.e.1-8) shoud be output to
column 5

Do I need to specify the range of cells to be scanned for the date??


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Jim,

1) Tell me if I'm getting warm. You want to count the number of
occurences
of a date in column D, and put random numbers next to those dates in
column
E.

Now, you don't say where the master StartDate is stored, the date that all
the other dates are compared to.

For the example code, let's say that the master start date is stored in
cell
A1.

Instead of your procedure Check_Start_Date, the single line

GroupCount = Application.CountIf(Range("D:D"), Range("A1"))

will count occurences of that date in column D of the activesheet.

That said, try running the first macro below (but copy both the sub and
the
function into the same codemodule - the sub uses the function to create
the
random numbers). When run, the sub will put random numbers next to the
dates
in column D that match the date value in cell A1.

If you need help modifying the code beyond that, post again.

HTH,
Bernie
MS Excel MVP

Sub JimsRandomNumbers()
Dim myRand As Variant
Dim myCnt As Integer
Dim i As Integer
Dim myCell As Range

myCnt = Application.CountIf(Range("D:D"), Range("A1"))
myRand = UniqueRands(1, myCnt, myCnt)

With Range("D:D")
Set myCell = .Find(Range("A1").Value)
myCell(1, 2).Value = myRand(LBound(myRand))

For i = 2 To myCnt
Set myCell = .FindNext(myCell)
myCell(1, 2).Value = myRand(i)
Next i
End With
End Sub

Function UniqueRands(ByVal MinNum As Long, _
ByVal MaxNum As Long, _
ByVal NumResults As Long) As Variant

Dim NumArr() As Long
Dim ResArr() As Long
Dim ResNdx As Long
Dim GetNdx As Long
Dim GetCounter As Long
Dim TempCounter As Long
Dim myTemp As Variant
Dim i As Integer
Dim j As Integer

If MinNum = MaxNum Then
UniqueRands = Null
Exit Function
End If
If NumResults Abs(MaxNum - MinNum + 1) Then
UniqueRands = Null
Exit Function
End If

ReDim NumArr(1 To Abs(MaxNum - MinNum + 1))
ReDim ResArr(1 To NumResults)

For ResNdx = 1 To UBound(NumArr)
NumArr(ResNdx) = MinNum + ResNdx - 1
Next ResNdx

For ResNdx = 1 To NumResults
GetCounter = Int((UBound(NumArr) * Rnd) + 1)
GetNdx = 1
TempCounter = 0
Do Until TempCounter = GetCounter
If GetNdx = UBound(NumArr) Then
GetNdx = 1
Else
GetNdx = GetNdx + 1
End If

If NumArr(GetNdx) = MinNum Then
TempCounter = TempCounter + 1
End If
Loop
ResArr(ResNdx) = NumArr(GetNdx)
NumArr(GetNdx) = MinNum - 1
Next ResNdx

UniqueRands = ResArr

End Function

"Jim Campbell" wrote in message
...
Bernie,
1)Date is stored in Lottery .xls in column 4 , want to get number with
same
date and store in GroupCount(see below)
if the date is different, then exit and go to step 2

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

2) Yes, the value from GroupCount will be used to generate the Random
number
, and within the value put out the random number to column 5 in the
Lottery.xls spreadsheet i.e.: GroupCount is 8, put out value from 1-8 in
column 5 all within the same date range(GroupCount with the same date)

3) I don't know if it is more efficient to output random number within
step
2 and a new value will be put out every time within the date range
criteria,
the new column will have the Random number generated from the function

Hope I made this clear, your input is appreciated

Jim,

You need to specify both the range and the cell with the actual value that
you are looking for.

Bernie


"Jim Campbell" wrote in message
...
The date is stored in column 4 of the Lottery.xls spreadsheet, and the
random number based upon the range of numbers(i.e.1-8) shoud be output to
column 5

Do I need to specify the range of cells to be scanned for the date??


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Jim,

1) Tell me if I'm getting warm. You want to count the number of
occurences
of a date in column D, and put random numbers next to those dates in
column
E.

Now, you don't say where the master StartDate is stored, the date that
all
the other dates are compared to.

For the example code, let's say that the master start date is stored in
cell
A1.

Instead of your procedure Check_Start_Date, the single line

GroupCount = Application.CountIf(Range("D:D"), Range("A1"))

will count occurences of that date in column D of the activesheet.

That said, try running the first macro below (but copy both the sub and
the
function into the same codemodule - the sub uses the function to create
the
random numbers). When run, the sub will put random numbers next to the
dates
in column D that match the date value in cell A1.

If you need help modifying the code beyond that, post again.

HTH,
Bernie
MS Excel MVP

Sub JimsRandomNumbers()
Dim myRand As Variant
Dim myCnt As Integer
Dim i As Integer
Dim myCell As Range

myCnt = Application.CountIf(Range("D:D"), Range("A1"))
myRand = UniqueRands(1, myCnt, myCnt)

With Range("D:D")
Set myCell = .Find(Range("A1").Value)
myCell(1, 2).Value = myRand(LBound(myRand))

For i = 2 To myCnt
Set myCell = .FindNext(myCell)
myCell(1, 2).Value = myRand(i)
Next i
End With
End Sub

Function UniqueRands(ByVal MinNum As Long, _
ByVal MaxNum As Long, _
ByVal NumResults As Long) As Variant

Dim NumArr() As Long
Dim ResArr() As Long
Dim ResNdx As Long
Dim GetNdx As Long
Dim GetCounter As Long
Dim TempCounter As Long
Dim myTemp As Variant
Dim i As Integer
Dim j As Integer

If MinNum = MaxNum Then
UniqueRands = Null
Exit Function
End If
If NumResults Abs(MaxNum - MinNum + 1) Then
UniqueRands = Null
Exit Function
End If

ReDim NumArr(1 To Abs(MaxNum - MinNum + 1))
ReDim ResArr(1 To NumResults)

For ResNdx = 1 To UBound(NumArr)
NumArr(ResNdx) = MinNum + ResNdx - 1
Next ResNdx

For ResNdx = 1 To NumResults
GetCounter = Int((UBound(NumArr) * Rnd) + 1)
GetNdx = 1
TempCounter = 0
Do Until TempCounter = GetCounter
If GetNdx = UBound(NumArr) Then
GetNdx = 1
Else
GetNdx = GetNdx + 1
End If

If NumArr(GetNdx) = MinNum Then
TempCounter = TempCounter + 1
End If
Loop
ResArr(ResNdx) = NumArr(GetNdx)
NumArr(GetNdx) = MinNum - 1
Next ResNdx

UniqueRands = ResArr

End Function

"Jim Campbell" wrote in message
...
Bernie,
1)Date is stored in Lottery .xls in column 4 , want to get number with
same
date and store in GroupCount(see below)
if the date is different, then exit and go to step 2

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

2) Yes, the value from GroupCount will be used to generate the Random
number
, and within the value put out the random number to column 5 in the
Lottery.xls spreadsheet i.e.: GroupCount is 8, put out value from 1-8
in
column 5 all within the same date range(GroupCount with the same date)

3) I don't know if it is more efficient to output random number within
step
2 and a new value will be put out every time within the date range
criteria,
the new column will have the Random number generated from the
function

Hope I made this clear, your input is appreciated

Jim,

I will send you a working file by private e-mail.

In the future, simply post with a request to send a file privately rather
than attaching the file and posting here.

HTH,
Bernie
MS Excel MVP

"Jim Campbell" wrote in message
...
Bernie,
Thanks again for the patience, I guess I have not been the clearest with
what I am trying to do.
There is no Master Date, the value of the date will change based upon the
number of people with the same start date.
That is why I was trying to loop to determine the number of occurrences
with
the same start date, and then put out the same number of Random Numbers
based upon that number

I have included a copy of the spreadsheet I have been testing this on as a
attachment.

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Jim,

You need to specify both the range and the cell with the actual value
that
you are looking for.

Bernie


"Jim Campbell" wrote in message
...
The date is stored in column 4 of the Lottery.xls spreadsheet, and the
random number based upon the range of numbers(i.e.1-8) shoud be output
to
column 5

Do I need to specify the range of cells to be scanned for the date??


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Jim,

1) Tell me if I'm getting warm. You want to count the number of
occurences
of a date in column D, and put random numbers next to those dates in
column
E.

Now, you don't say where the master StartDate is stored, the date
that
all
the other dates are compared to.

For the example code, let's say that the master start date is stored
in
cell
A1.

Instead of your procedure Check_Start_Date, the single line

GroupCount = Application.CountIf(Range("D:D"), Range("A1"))

will count occurences of that date in column D of the activesheet.

That said, try running the first macro below (but copy both the sub
and
the
function into the same codemodule - the sub uses the function to
create
the
random numbers). When run, the sub will put random numbers next to
the
dates
in column D that match the date value in cell A1.

If you need help modifying the code beyond that, post again.

HTH,
Bernie
MS Excel MVP

Sub JimsRandomNumbers()
Dim myRand As Variant
Dim myCnt As Integer
Dim i As Integer
Dim myCell As Range

myCnt = Application.CountIf(Range("D:D"), Range("A1"))
myRand = UniqueRands(1, myCnt, myCnt)

With Range("D:D")
Set myCell = .Find(Range("A1").Value)
myCell(1, 2).Value = myRand(LBound(myRand))

For i = 2 To myCnt
Set myCell = .FindNext(myCell)
myCell(1, 2).Value = myRand(i)
Next i
End With
End Sub

Function UniqueRands(ByVal MinNum As Long, _
ByVal MaxNum As Long, _
ByVal NumResults As Long) As Variant

Dim NumArr() As Long
Dim ResArr() As Long
Dim ResNdx As Long
Dim GetNdx As Long
Dim GetCounter As Long
Dim TempCounter As Long
Dim myTemp As Variant
Dim i As Integer
Dim j As Integer

If MinNum = MaxNum Then
UniqueRands = Null
Exit Function
End If
If NumResults Abs(MaxNum - MinNum + 1) Then
UniqueRands = Null
Exit Function
End If

ReDim NumArr(1 To Abs(MaxNum - MinNum + 1))
ReDim ResArr(1 To NumResults)

For ResNdx = 1 To UBound(NumArr)
NumArr(ResNdx) = MinNum + ResNdx - 1
Next ResNdx

For ResNdx = 1 To NumResults
GetCounter = Int((UBound(NumArr) * Rnd) + 1)
GetNdx = 1
TempCounter = 0
Do Until TempCounter = GetCounter
If GetNdx = UBound(NumArr) Then
GetNdx = 1
Else
GetNdx = GetNdx + 1
End If

If NumArr(GetNdx) = MinNum Then
TempCounter = TempCounter + 1
End If
Loop
ResArr(ResNdx) = NumArr(GetNdx)
NumArr(GetNdx) = MinNum - 1
Next ResNdx

UniqueRands = ResArr

End Function

"Jim Campbell" wrote in message
...
Bernie,
1)Date is stored in Lottery .xls in column 4 , want to get number
with
same
date and store in GroupCount(see below)
if the date is different, then exit and go to step 2

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

2) Yes, the value from GroupCount will be used to generate the
Random
number
, and within the value put out the random number to column 5 in
the
Lottery.xls spreadsheet i.e.: GroupCount is 8, put out value from
1-8
in
column 5 all within the same date range(GroupCount with the same
date)

3) I don't know if it is more efficient to output random number
within
step
2 and a new value will be put out every time within the date range
criteria,
the new column will have the Random number generated from the
function

Hope I made this clear, your input is appreciated

Thanks Bernie, I have it and it works great, just following through the code
trying to make some comments so I understand what is is doing and I may have
some questions for you if thats ok.
Your help is greatly appreciated


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Jim,

I will send you a working file by private e-mail.

In the future, simply post with a request to send a file privately rather
than attaching the file and posting here.

HTH,
Bernie
MS Excel MVP

"Jim Campbell" wrote in message
...
Bernie,
Thanks again for the patience, I guess I have not been the clearest with
what I am trying to do.
There is no Master Date, the value of the date will change based upon
the
number of people with the same start date.
That is why I was trying to loop to determine the number of occurrences
with
the same start date, and then put out the same number of Random Numbers
based upon that number

I have included a copy of the spreadsheet I have been testing this on as
a
attachment.

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Jim,

You need to specify both the range and the cell with the actual value
that
you are looking for.

Bernie


"Jim Campbell" wrote in message
...
The date is stored in column 4 of the Lottery.xls spreadsheet, and
the
random number based upon the range of numbers(i.e.1-8) shoud be
output
to
column 5

Do I need to specify the range of cells to be scanned for the
date??


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Jim,

1) Tell me if I'm getting warm. You want to count the number of
occurences
of a date in column D, and put random numbers next to those dates
in
column
E.

Now, you don't say where the master StartDate is stored, the date
that
all
the other dates are compared to.

For the example code, let's say that the master start date is
stored
in
cell
A1.

Instead of your procedure Check_Start_Date, the single line

GroupCount = Application.CountIf(Range("D:D"), Range("A1"))

will count occurences of that date in column D of the activesheet.

That said, try running the first macro below (but copy both the
sub
and
the
function into the same codemodule - the sub uses the function to
create
the
random numbers). When run, the sub will put random numbers next to
the
dates
in column D that match the date value in cell A1.

If you need help modifying the code beyond that, post again.

HTH,
Bernie
MS Excel MVP

Sub JimsRandomNumbers()
Dim myRand As Variant
Dim myCnt As Integer
Dim i As Integer
Dim myCell As Range

myCnt = Application.CountIf(Range("D:D"), Range("A1"))
myRand = UniqueRands(1, myCnt, myCnt)

With Range("D:D")
Set myCell = .Find(Range("A1").Value)
myCell(1, 2).Value = myRand(LBound(myRand))

For i = 2 To myCnt
Set myCell = .FindNext(myCell)
myCell(1, 2).Value = myRand(i)
Next i
End With
End Sub

Function UniqueRands(ByVal MinNum As Long, _
ByVal MaxNum As Long, _
ByVal NumResults As Long) As Variant

Dim NumArr() As Long
Dim ResArr() As Long
Dim ResNdx As Long
Dim GetNdx As Long
Dim GetCounter As Long
Dim TempCounter As Long
Dim myTemp As Variant
Dim i As Integer
Dim j As Integer

If MinNum = MaxNum Then
UniqueRands = Null
Exit Function
End If
If NumResults Abs(MaxNum - MinNum + 1) Then
UniqueRands = Null
Exit Function
End If

ReDim NumArr(1 To Abs(MaxNum - MinNum + 1))
ReDim ResArr(1 To NumResults)

For ResNdx = 1 To UBound(NumArr)
NumArr(ResNdx) = MinNum + ResNdx - 1
Next ResNdx

For ResNdx = 1 To NumResults
GetCounter = Int((UBound(NumArr) * Rnd) + 1)
GetNdx = 1
TempCounter = 0
Do Until TempCounter = GetCounter
If GetNdx = UBound(NumArr) Then
GetNdx = 1
Else
GetNdx = GetNdx + 1
End If

If NumArr(GetNdx) = MinNum Then
TempCounter = TempCounter + 1
End If
Loop
ResArr(ResNdx) = NumArr(GetNdx)
NumArr(GetNdx) = MinNum - 1
Next ResNdx

UniqueRands = ResArr

End Function

"Jim Campbell" wrote in message
...
Bernie,
1)Date is stored in Lottery .xls in column 4 , want to get
number
with
same
date and store in GroupCount(see below)
if the date is different, then exit and go to step 2

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

2) Yes, the value from GroupCount will be used to generate the
Random
number
, and within the value put out the random number to column 5 in
the
Lottery.xls spreadsheet i.e.: GroupCount is 8, put out value
from
1-8
in
column 5 all within the same date range(GroupCount with the same
date)

3) I don't know if it is more efficient to output random number
within
step
2 and a new value will be put out every time within the date
range
criteria,
the new column will have the Random number generated from the
function

Hope I made this clear, your input is appreciated