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?

[Webinar] Streamline your web hosting managementRegister Today

kittenwhiskyConnect With a Mentor Commented:
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

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!!!
All Courses

From novice to tech pro — start learning today.