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.
EaglemoonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

patrickabCommented:
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

Open in new window

0
EaglemoonAuthor 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
bromy2004Commented:
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
    'Add winnings to dblProfit
    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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EaglemoonAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.