Excel VBA Question

A selection of five random digits which is the “winning number.”  The user should be able to buy as many tickets (each containing five random numbers) as they would like at \$1 per ticket.   If one of the user ticket matches the winning number, the user wins \$100,000.  Assuming that  order matters, if the winning number is 21345, then 12345 doesn’t win.)

I am trying to write a sub that will first, generates a random winning number, then stores it in a string variable so that you can use string concatenation,  next it will asks the how many cards user wants to buy. It must then use a For Loop to generate this many cards and store their number in a card array which should be a string array.  Next, it should use a DO LOOP to keep checking cards until a winner has been found or no more cards remain.  Lastly,  it must display a message saying whether you are a winner and what your net gain or loss.  A single random digit from 0 to 9 can be generate with excel’s RandBetween Function.
Who is Participating?

Commented:
Hi EagleMoon,

Attached is the macro.

i see you're new to Experts Exchange so please keep in mind that:
1. The Experts helping you are volunteers, we don't get anything from this, we are just helping.
2. The Experts are helpers...so we would try to point you in the right direction rather that just give you the answer, That's how everyone learn's.

The macro:
1. Generates the winning number storing it as x-x-x-x-x
2. asks the user how many tickets they want
3. Uses the same function as the winning number to get x amount of tickets
4. Loops through the tickets to check the winner,
If it is a winner dblProfit gets PrizeMoney added to it.
5. dblProfit = dblProfit - (TicketPrice * TotalTickets) (Take out cost of tickets
6. Lets the use know the outcome
Option Explicit

Sub Lottery()
'This sub selects five random digits. This is the "winning number"
'You can buy as many lottery cards as you like at \$1 per card.
'Each card contains five random digits.
'If you get a card that matches the winning number, You win \$100,000.

'Upper and lower numbers
'Normally 0 to 9 but can be up to Integer limit
Const LowerBound    As Integer = 0
Const UpperBound    As Integer = 9

'How many numbers in the Lottery
'Default 5
'Can be any up to Integer limit
Const TotalDigits   As Integer = 5

'Price per ticket
Const TicketPrice   As Double = 1
'Prize Pool
Const PrizeMoney    As Double = 100000

'winning number
Dim WinNum          As String
'user's Tickets
Dim TicketNumber()  As String
'How many tickets the user wants
Dim TotalTickets    As Long
'Profit for the user
Dim dblProfit       As Double
'Cost for user
Dim dblCost         As Double

Dim i               As Integer

'Get winning Number
WinNum = RndNumber(LowerBound, UpperBound, TotalDigits)

TotalTickets = Application.InputBox("Please enter the number of tickets you would like to purchase?", _
"How many tickets?", _
1, _
Type:=1)
If TotalTickets < 1 Then: MsgBox ("Not enough tickets"): Exit Sub

'Generate user's Tickets
ReDim TicketNumber(1 To TotalTickets)
For i = 1 To TotalTickets
TicketNumber(i) = RndNumber(LowerBound, UpperBound, TotalDigits)
Next i

'Find winning ticket
For i = 1 To TotalTickets
If TicketNumber(i) = WinNum Then
dblProfit = dblProfit + PrizeMoney
End If
Next i

'Take cost from Profit
dblProfit = dblProfit - (TicketPrice * TotalTickets)

MsgBox Prompt:="Winning ticket: " & WinNum & vbNewLine _
& "Your Net Profit= " & Format(dblProfit, "\$#,##0.00") & vbNewLine _
& "You won " & Round(dblProfit / PrizeMoney, 0) & " time(s)", _
Buttons:=vbOKOnly, _
Title:="Winnings"
End Sub

Function RndNumber(ByVal LowerBound As Integer, _
ByVal UpperBound As Integer, _
ByVal TotalDigits As Integer) As String
Dim i As Integer
Dim Nums() As String
ReDim Nums(1 To TotalDigits)

For i = 1 To TotalDigits
Randomize
Nums(i) = CStr(Int(((UpperBound - LowerBound) * Rnd) + LowerBound))
Next i

RndNumber = Join(Nums, "-")
End Function
0

Commented:
Eaglemoon,

When creating a lottery system which depends on the selction of one number to win, then only one random number needs to be generated - and that is the winning number. Obviously the winning number should not be known to anyone.

All the ticket numbers can and should be purely sequential. There need be nothing random about them. Indeed if one is to ensure that only tickets with unique numbers are sold it is essential to use purely sequential numbers.

So taking for example of a lottery system that has a 5 digit number as the winning number then the macro below will generate a number between 10000 and 20000. That's the only random element that is needed to run a lottery. It need only be generated after the tickets have been sold and the lottery closed.

Patrick
Sub winning_no()
Randomize
Sheets("Sheet2").[A1] = Int((10000 * Rnd) + 10000)
End Sub
0

Author Commented:
Hi Mr. Patrick,
You did not answer my question.  The fact remain that I need a sub that can do what I wrote.  I am trying to conduct research, so all the stuff with the winning numbe rshould not be know to anyone does really matter because it and experiment.  So please let me know if you can help me or not.

This is what I have so far:
Sub Lottery()

'This sub selects five random digits. This is the "winning number"
'You can buy as many lottery cards as you like at \$1 per card.
'Each card contains five random digits.
'If you get a card that matches the winning number, You win \$100,000.

Const UpperBound As Integer = 9, LowerBound As Integer = 0
Const TotalDigits As Integer = 5, TicketPrice As Double = 1
Const PrizeMoney As Double = 100000

Dim i As Integer, Lottery As String
Dim WinningNumber As String, TicketNumber() As String
Dim TotalTickets As Long
Dim NetProfit As Double, Msg As String

TotalTickets = Application.InputBox("Please enter the number of tickets you would like to purchase?", "How many tickets?", 1, Type:=1)
If TotalTickets = 0 Then Exit Sub
0

Author Commented:
Wow!!! Absolutely amazing!! I taught they paid the experts.  I hope I can get as good as you one day.  I just decided to start using VBA for my job.  I work as a research assistant to multiple people and realize that VBA can speed up alot of the repetitive things I do.

Thanks Again!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.