help to adjust my code

A

Anthony

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
 
T

Tom Ogilvy

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"
if len(Trim(Range("AB1").Value)) = 0 Then
Banker
Elseif Range("AB1").Value = 1 then
Banker2
Else
'neither 1 or empty, do nothing
End if
Else

--
Regards,
Tom Ogilvy

Anthony said:
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
Click to expand...
 
A

Anthony

Tom, You are a star - thanks very much !

Tom Ogilvy said:
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"
if len(Trim(Range("AB1").Value)) = 0 Then
Banker
Elseif Range("AB1").Value = 1 then
Banker2
Else
'neither 1 or empty, do nothing
End if
Else

--
Regards,
Tom Ogilvy

Anthony said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Coding Help for Beginner 0
Help in adding another check to my code 1
VBA-User says active cell jumps to a different sheet upon Save. Please review my code 1
VBA for Dependents shortcut menu 0
Code does not error but no copy either 4
VBA multiple list of values for variable 1
Macro to Select text from a point given to a specific word 2
Word macro that can call cells out of Excel for a find and replace 0

Top