Improved the error trap in the code above and added the reset for the restults, so you can re-run without deleting the last results now. It also now lists the final tally to the botom of the results. Then your results are added to a log of cycle results. So, you should use this version.
Sub myRoulette()
'Standard module, like: Module1.
Dim mySpin%, maxNum%, numSpins%, spins%, myNum%, myEnd%, myBetC%
Dim myWinings&, myLosses&, myWiner&, myLose&, myBet&
Dim myFlagW As Boolean, myFlagL As Boolean, myLog As Boolean
Dim mySht As Worksheet
Dim winNum$, myResult$
Dim myRng As Range
Worksheets(1).Select
ActiveSheet.Name = "Roulette"
'Get bet Numbers!
myEnd = ActiveSheet.Range("IV2").End(xlToLeft).Column
Set myRng = ActiveSheet.Range(Cells(2, 1), Cells(2, myEnd))
'Add Title.
ActiveSheet.Range("D1").Select
ActiveSheet.Range("A1").Value = "Roulette, Gaming!"
ActiveSheet.Range("A1").Font.Bold = True
ActiveSheet.Range("A1").Font.Size = 16
'Test for no bets, Error!
If (myRng.Count = 1 And ActiveSheet.Range("A2").Value = "") Then
ActiveSheet.Range("A2").Select
MsgBox "In Row ""2"" please add:" & vbLf & _
"The numbers you wish to bet on, " & _
"starting in Column ""A""" & vbLf & _
"Only add one value to a Column/Cell!" & vbLf & vbLf & _
"Note: You may use 0 & 00, entered as text only," & vbLf & _
"enter them with a single quote first!"
Exit Sub
End If
'Add Labels.
ActiveSheet.Range("A3").Value = "Spin"
ActiveSheet.Range("B3").Value = "This Bet"
ActiveSheet.Range("C3").Value = "Losses"
ActiveSheet.Range("D3").Value = "Wins"
ActiveSheet.Range("E3").Value = "This Win"
ActiveSheet.Range("F3").Value = "Winner"
ActiveSheet.Range("G3").Value = "Wheel"
ActiveSheet.Rows("3:3").Font.Bold = True
ActiveSheet.Columns("F:F").HorizontalAlignment = xlCenter
If ActiveSheet.Range("A5").Value <> "" Then _
ActiveSheet.Range(Cells(5, 1), Cells(ActiveSheet.Range("A65536").End(xlUp).Row, 7)).Delete
'Get wheel!
maxNum = InputBox("What is the highest number on the wheel?" & vbLf & vbLf & _
"Note: The last two numbers will be converted to 0 and 00." & vbLf & _
"So, for 1 to 36 with 0 and 00, enter 38!", "Numbers to use!", 38)
'Get bet!
myBet = InputBox("What amount to add to the amount bet each spin?", "Enter bet pattern!", 5)
myBetC = myBet
'Get Number of spins/bets to make!
numSpins = InputBox("How many spins will you be making?", "Get Spin Cycles!", 100)
For spins = 1 To numSpins
'Initialize random-number generator.
Randomize
'Get random number for spin.
mySpin = Int((maxNum * Rnd) + 1)
'Get win or loss!
For Each Cell In myRng
If mySpin = maxNum Then
winNum = "00"
myFlagW = True
End If
If mySpin = (maxNum - 1) Then
winNum = "0"
myFlagW = True
End If
If (mySpin = Cell.Value And myFlagW = False) Then
winNum = mySpin
myFlagW = True
End If
ActiveSheet.Columns("F:F").NumberFormat = "@"
ActiveSheet.Columns("F:F").HorizontalAlignment = xlCenter
Next Cell
'Print win-loss info to sheet!
If myFlagW = False Then
'Tally losses.
myLose = myLose + myBet
ActiveSheet.Range("C" & 4 + spins).Value = "Lose"
myFlagL = True
End If
If myFlagL = False Then
'Calculate amount won?
myWiner = (myBet * (((myRng.Count / maxNum) / 2) * 100)) - ((myRng.Count - 1) * myBet)
ActiveSheet.Range("D" & 4 + spins).Value = "Win"
ActiveSheet.Range("E" & 4 + spins).Value = _
Application.WorksheetFunction.Text(myWiner, "$#,###")
ActiveSheet.Range("F" & 4 + spins).Value = winNum
End If
ActiveSheet.Range("A" & 4 + spins).Value = spins
ActiveSheet.Range("B" & 4 + spins).Value = _
Application.WorksheetFunction.Text(myBet, "$#,###")
myBet = myBet + myBetC
If myFlagW = True Then myWinings = myWinings + myWiner
If myFlagL = True Then myLosses = myLosses + myBet
ActiveSheet.Range("G" & 4 + spins).Value = mySpin
myFlagW = False
myFlagL = False
Next spins
If myWinings - myLosses > 0 Then
myResult = "profit"
Else
myResult = "loss"
End If
'Results message!
MsgBox "You spent: " & _
Application.WorksheetFunction.Text(myLosses, "$#,###") & vbLf & _
"You won: " & Application.WorksheetFunction.Text(myWinings, "$#,###") & _
vbLf & "Your " & myResult & " is: " & _
Application.WorksheetFunction.Text(Abs(myWinings - myLosses), "$#,###")
'Print results to sheet!
ActiveSheet.Range("A" & 6 + spins).Value = _
"You spent: " & _
Application.WorksheetFunction.Text(myLosses, "$#,###") & _
". You won: " & Application.WorksheetFunction.Text(myWinings, "$#,###") & _
". Your " & myResult & " is: " & _
Application.WorksheetFunction.Text(Abs(myWinings - myLosses), "$#,###") & "."
ActiveSheet.Range("A" & 7 + spins).Select
'Check for "Log" Sheet or Make "Log" Sheet!
For Each ws In Worksheets
If ws.Name = "Log" Then myLog = True
Next ws
If myLog = False Then
Set mySht = ActiveWorkbook.Sheets.Add
mySht.Name = "Log"
Sheets("Log").Move After:=Sheets(2)
End If
'Post results to log.
Sheets("Log").Range("A65536").End(xlUp).Offset(1, 0).Value = Date & "' " & Time
Sheets("Log").Range("A65536").End(xlUp).Offset(0, 1).Value = " Spent:"
Sheets("Log").Range("A65536").End(xlUp).Offset(0, 2).Value = _
Application.WorksheetFunction.Text(myLosses, "$#,###")
Sheets("Log").Range("A65536").End(xlUp).Offset(0, 3).Value = " Won:"
Sheets("Log").Range("A65536").End(xlUp).Offset(0, 4).Value = _
Application.WorksheetFunction.Text(myWinings, "$#,###")
Sheets("Log").Range("A65536").End(xlUp).Offset(0, 5).Value = " " & myResult & ":"
Sheets("Log").Range("A65536").End(xlUp).Offset(0, 6).Value = _
Application.WorksheetFunction.Text(Abs(myWinings - myLosses), "$#,###")
Sheets("Log").Range("A65536").End(xlUp).EntireRow.HorizontalAlignment = xlRight
Sheets("Log").Columns("A:G").Columns.AutoFit
Sheets("Log").Select
Sheets("Log").Range("A65536").End(xlUp).Select
Sheets("Roulette").Select
End Sub
