Excel VBA Question (Please utilize For Loop & Do Loop)

There is a selection of five random digits that 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?
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.

try this
Sub Lottery()
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("How many tickets would you like to buy?", "How many tickets?", 1, Type:=1)
If TotalTickets = 0 Then Exit Sub

ReDim TicketNumber(1 To TotalTickets)
For i = 1 To TotalTickets + 1
    Lottery = ""
    Do Until Len(Lottery) = TotalDigits
    Lottery = Lottery & Int((UpperBound - LowerBound + 1) * Rnd + LowerBound)
    If i = 1 Then
        WinningNumber = Lottery
        TicketNumber(i - 1) = Lottery
    End If
Next i

Msg = "We're sorry, you didn't get the winning ticket."
For i = 1 To TotalTickets
    If TicketNumber(i) = WinningNumber Then
        NetProfit = NetProfit + PrizeMoney
        Msg = "Congratulations, you have a winning ticket!"
        Exit For
    End If
Next i
NetProfit = NetProfit - TotalTickets * TicketPrice

Msg = Msg & vbCrLf & "Winning number: " & vbTab & vbTab & WinningNumber & vbCrLf
Msg = Msg & "Net profit: " & vbTab & vbTab & NetProfit
Msg = Msg & vbCrLf & "Your ticket numbers: "
For i = 1 To TotalTickets
    Msg = Msg & vbCrLf & vbTab & vbTab & vbTab & TicketNumber(i)
Next i
MsgBox Msg, vbOKOnly, "LotteryResult"

End Sub

Open in new window


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
Patrick MatthewsCommented:

With respect, this question appears to be an academic assignment.  Putting that aside for the moment...
  • Why is it so important that you use a For...Next loop to generate the cards?  That is certainly one way to do it, but hardly the only way
  • Again, why is it so important to use Do...Loop t check the cards for the winner?  Again, while that is one way to do it, there are other, equally valid ways
  • You have not specified whether this is really a raffle, or a lottery.  The difference is important: in a raffle, a winning ticket has always been sold, but in a lottery, (a) there not be a winning ticket sold and (b) more than one ticket with the winning number(s) may have been sold
Please clarify whether this question is related to an academic assignment.

MasterOfTheSkyAuthor Commented:
Matthewspatrick my co workers and I are conducting an experiment on lottery tickets.  Thank you!!
MasterOfTheSkyAuthor Commented:
Thank You very much!! It works!!!
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.