asked on

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.

patrickab

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

Eaglemoon

ASKER

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

ASKER CERTIFIED SOLUTION

bromy2004

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eaglemoon

ASKER

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