G
Guest
Hi,
with lots and lots of help I have managed to put this code together.
The main part of it follows this sequence.
1) Check value in A1 and if it is = 0, 3, 6, 9, 12, 15 then run BANKER macro
2) if value in A1 doesn't = 0, 3, 6, 9, 12, 15 then check if there is a
value found in cell B9 (result from hidden formula
=IF(ISBLANK('1'!$E$14),"",'1'!$E$14) )
If value found in B9 then msg box "box already used..bla bla"
if no value found run rest of macro..
the code....
Sub box1()
v = 0
Range("A2").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C)-1"
Range("A2").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A2").Select
Application.CutCopyMode = False
Selection.ClearContents
If Range("A1").Value = 15 Or Range("A1").Value = 12 Or Range("A1").Value = 9
Or Range("A1").Value = 6 Or Range("A1").Value = 3 Or Range("A1").Value = 0
And Range("ab1").Value = "" Then
MsgBox "Whats that phone ringing? - must be the Banker!", vbExclamation,
"Deal Or No Deal"
Banker
Else
If IsNumeric(Range("B9")) And Range("B9") > 0.5 Or Range("B9").Value = "1p"
Or Range("B9").Value = "10p" Or Range("B9").Value = "50p" Then
MsgBox "Box already picked..chose another !", vbExclamation, "Deal or No
Deal?"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C)+1"
Range("A2").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Else
Sheets("1").Select
Rows("14:17").Select
Selection.EntireRow.Hidden = True
Range("a1").Select
Application.Wait Now() + TimeSerial(0, 0, 3)
v = 0
Application.ScreenUpdating = False
mynumber = ""
Do While mynumber = ""
x = Int((57 - 32 + 1) * Rnd + 32)
If Worksheets("sheet1").Cells(x, 18).Value <>
Sheets("Sheet1").Range("N27").Value Then
mynumber = Worksheets("sheet1").Cells(x, 18).Value
Worksheets("sheet1").Cells(x, 18).Clear
Worksheets("1").Cells(14, 5).Value = mynumber
End If
Loop
Rows("14:17").Select
Selection.EntireRow.Hidden = False
Range("A1").Select
End If
End If
End Sub
What I would like added is another check,
at the moment the code Checks value in A1 and if it is = 0, 3, 6, 9, 12, 15
then run BANKER macro.
What I now want it to do.....
Before running the BANKER macro, first check value in cell AB1 and if this
is equal to "1" then run BANKER2 macro, if there is no value in AB1 run
BANKER macro
Hope I have explained this and hope you can help even more
with lots and lots of help I have managed to put this code together.
The main part of it follows this sequence.
1) Check value in A1 and if it is = 0, 3, 6, 9, 12, 15 then run BANKER macro
2) if value in A1 doesn't = 0, 3, 6, 9, 12, 15 then check if there is a
value found in cell B9 (result from hidden formula
=IF(ISBLANK('1'!$E$14),"",'1'!$E$14) )
If value found in B9 then msg box "box already used..bla bla"
if no value found run rest of macro..
the code....
Sub box1()
v = 0
Range("A2").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C)-1"
Range("A2").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A2").Select
Application.CutCopyMode = False
Selection.ClearContents
If Range("A1").Value = 15 Or Range("A1").Value = 12 Or Range("A1").Value = 9
Or Range("A1").Value = 6 Or Range("A1").Value = 3 Or Range("A1").Value = 0
And Range("ab1").Value = "" Then
MsgBox "Whats that phone ringing? - must be the Banker!", vbExclamation,
"Deal Or No Deal"
Banker
Else
If IsNumeric(Range("B9")) And Range("B9") > 0.5 Or Range("B9").Value = "1p"
Or Range("B9").Value = "10p" Or Range("B9").Value = "50p" Then
MsgBox "Box already picked..chose another !", vbExclamation, "Deal or No
Deal?"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C)+1"
Range("A2").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Else
Sheets("1").Select
Rows("14:17").Select
Selection.EntireRow.Hidden = True
Range("a1").Select
Application.Wait Now() + TimeSerial(0, 0, 3)
v = 0
Application.ScreenUpdating = False
mynumber = ""
Do While mynumber = ""
x = Int((57 - 32 + 1) * Rnd + 32)
If Worksheets("sheet1").Cells(x, 18).Value <>
Sheets("Sheet1").Range("N27").Value Then
mynumber = Worksheets("sheet1").Cells(x, 18).Value
Worksheets("sheet1").Cells(x, 18).Clear
Worksheets("1").Cells(14, 5).Value = mynumber
End If
Loop
Rows("14:17").Select
Selection.EntireRow.Hidden = False
Range("A1").Select
End If
End If
End Sub
What I would like added is another check,
at the moment the code Checks value in A1 and if it is = 0, 3, 6, 9, 12, 15
then run BANKER macro.
What I now want it to do.....
Before running the BANKER macro, first check value in cell AB1 and if this
is equal to "1" then run BANKER2 macro, if there is no value in AB1 run
BANKER macro
Hope I have explained this and hope you can help even more