| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help to adjust my code
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
help to adjust my code
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" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
help to adjust my code
Tom, You are a star - thanks very much !
"Tom Ogilvy" wrote: 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" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I need to adjust the date | Excel Discussion (Misc queries) | |||
| Using Code to Adjust Specific Column Width | Excel Programming | |||
| Help to adjust code | Excel Programming | |||
| HELP - I need to adjust code!!!! | Excel Programming | |||
Hybrid Mode
