Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > macro to run IF condition met
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default macro to run IF condition met
please help.... I need to have a macro run under the following criteria:

if cell A1 = 0 then run macro
if cell A1 0 then don't run macro and show message box to say "delete list"

below is the begining of the existing code ...... thanks

Range("B9:I16").Select
ActiveSheet.Unprotect
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,939
Default macro to run IF condition met
if range("A1").value = 0 then
Range("B9:I16").Select
ActiveSheet.Unprotect
else
msgbox "delete list"
end if
--
HTH...

Jim Thomlinson


"Savuti" wrote:

please help.... I need to have a macro run under the following criteria:

if cell A1 = 0 then run macro
if cell A1 0 then don't run macro and show message box to say "delete list"

below is the begining of the existing code ...... thanks

Range("B9:I16").Select
ActiveSheet.Unprotect
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default macro to run IF condition met
Jim thanks for the reply. I did copy and paste your code, I got a Compile
error stating - Else without if.

I have included my complete existing code. Appreciate the help.

Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Select
Selection.Copy
Range("B16:I16").Select
ActiveSheet.Paste
Range("B14:I14").Select
Application.CutCopyMode = False
Selection.Copy
Range("B15:I15").Select
ActiveSheet.Paste
Range("B13:I13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B14:I14").Select
ActiveSheet.Paste
Range("B12:I12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B13:I13").Select
ActiveSheet.Paste
Range("B11:I11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B12:I12").Select
ActiveSheet.Paste
Range("B10:I10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B11:I11").Select
ActiveSheet.Paste
Range("B9:I9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B10:I10").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B9:I9").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B9:I9").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("I7").Select
End Sub

"Jim Thomlinson" wrote:

if range("A1").value = 0 then
Range("B9:I16").Select
ActiveSheet.Unprotect
else
msgbox "delete list"
end if
--
HTH...

Jim Thomlinson


"Savuti" wrote:

please help.... I need to have a macro run under the following criteria:

if cell A1 = 0 then run macro
if cell A1 0 then don't run macro and show message box to say "delete list"

below is the begining of the existing code ...... thanks

Range("B9:I16").Select
ActiveSheet.Unprotect
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,939
Default macro to run IF condition met
Sub TEST()
If Range("A1").Value = 0 Then
Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Select
Selection.Copy
Range("B16:I16").Select
ActiveSheet.Paste
Range("B14:I14").Select
Application.CutCopyMode = False
Selection.Copy
Range("B15:I15").Select
ActiveSheet.Paste
Range("B13:I13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B14:I14").Select
ActiveSheet.Paste
Range("B12:I12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B13:I13").Select
ActiveSheet.Paste
Range("B11:I11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B12:I12").Select
ActiveSheet.Paste
Range("B10:I10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B11:I11").Select
ActiveSheet.Paste
Range("B9:I9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B10:I10").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B9:I9").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B9:I9").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If

End Sub

--
HTH...

Jim Thomlinson


"Savuti" wrote:

Jim thanks for the reply. I did copy and paste your code, I got a Compile
error stating - Else without if.

I have included my complete existing code. Appreciate the help.

Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Select
Selection.Copy
Range("B16:I16").Select
ActiveSheet.Paste
Range("B14:I14").Select
Application.CutCopyMode = False
Selection.Copy
Range("B15:I15").Select
ActiveSheet.Paste
Range("B13:I13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B14:I14").Select
ActiveSheet.Paste
Range("B12:I12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B13:I13").Select
ActiveSheet.Paste
Range("B11:I11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B12:I12").Select
ActiveSheet.Paste
Range("B10:I10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B11:I11").Select
ActiveSheet.Paste
Range("B9:I9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B10:I10").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B9:I9").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B9:I9").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("I7").Select
End Sub

"Jim Thomlinson" wrote:

if range("A1").value = 0 then
Range("B9:I16").Select
ActiveSheet.Unprotect
else
msgbox "delete list"
end if
--
HTH...

Jim Thomlinson


"Savuti" wrote:

please help.... I need to have a macro run under the following criteria:

if cell A1 = 0 then run macro
if cell A1 0 then don't run macro and show message box to say "delete list"

below is the begining of the existing code ...... thanks

Range("B9:I16").Select
ActiveSheet.Unprotect
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 152
Default macro to run IF condition met
Another method which will limit the number of selections:

Sub Test()
If Range("A1").Value = 0 Then
Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Copy Destination:=Range("B16:I16")
Range("B14:I14").Copy Destination:=Range("B15:I15")
Range("B13:I13").Copy Destination:=Range("B14:I14")
Range("B12:I12").Copy Destination:=Range("B13:I13")
Range("B11:I11").Copy Destination:=Range("B12:I12")
Range("B10:I10").Copy Destination:=Range("B11:I11")
Range("B9:I9").Copy Destination:=Range("B10:I10")
Range("B7:I7").Copy Destination:=Range("B9:I9")
Range("B7:I7").ClearContents
With Range("B9:I9")
.Locked = True
.FormulaHidden = False
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If
End Sub

Alan



"Savuti" wrote:

Jim thanks for the reply. I did copy and paste your code, I got a Compile
error stating - Else without if.

I have included my complete existing code. Appreciate the help.

Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Select
Selection.Copy
Range("B16:I16").Select
ActiveSheet.Paste
Range("B14:I14").Select
Application.CutCopyMode = False
Selection.Copy
Range("B15:I15").Select
ActiveSheet.Paste
Range("B13:I13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B14:I14").Select
ActiveSheet.Paste
Range("B12:I12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B13:I13").Select
ActiveSheet.Paste
Range("B11:I11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B12:I12").Select
ActiveSheet.Paste
Range("B10:I10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B11:I11").Select
ActiveSheet.Paste
Range("B9:I9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B10:I10").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B9:I9").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B9:I9").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("I7").Select
End Sub

"Jim Thomlinson" wrote:

if range("A1").value = 0 then
Range("B9:I16").Select
ActiveSheet.Unprotect
else
msgbox "delete list"
end if
--
HTH...

Jim Thomlinson


"Savuti" wrote:

please help.... I need to have a macro run under the following criteria:

if cell A1 = 0 then run macro
if cell A1 0 then don't run macro and show message box to say "delete list"

below is the begining of the existing code ...... thanks

Range("B9:I16").Select
ActiveSheet.Unprotect
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default macro to run IF condition met
Jim, you are a genius, works perfect - thank you sir

"Jim Thomlinson" wrote:

Sub TEST()
If Range("A1").Value = 0 Then
Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Select
Selection.Copy
Range("B16:I16").Select
ActiveSheet.Paste
Range("B14:I14").Select
Application.CutCopyMode = False
Selection.Copy
Range("B15:I15").Select
ActiveSheet.Paste
Range("B13:I13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B14:I14").Select
ActiveSheet.Paste
Range("B12:I12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B13:I13").Select
ActiveSheet.Paste
Range("B11:I11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B12:I12").Select
ActiveSheet.Paste
Range("B10:I10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B11:I11").Select
ActiveSheet.Paste
Range("B9:I9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B10:I10").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B9:I9").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B9:I9").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If

End Sub

--
HTH...

Jim Thomlinson


"Savuti" wrote:

Jim thanks for the reply. I did copy and paste your code, I got a Compile
error stating - Else without if.

I have included my complete existing code. Appreciate the help.

Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Select
Selection.Copy
Range("B16:I16").Select
ActiveSheet.Paste
Range("B14:I14").Select
Application.CutCopyMode = False
Selection.Copy
Range("B15:I15").Select
ActiveSheet.Paste
Range("B13:I13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B14:I14").Select
ActiveSheet.Paste
Range("B12:I12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B13:I13").Select
ActiveSheet.Paste
Range("B11:I11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B12:I12").Select
ActiveSheet.Paste
Range("B10:I10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B11:I11").Select
ActiveSheet.Paste
Range("B9:I9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B10:I10").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B9:I9").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B9:I9").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("I7").Select
End Sub

"Jim Thomlinson" wrote:

if range("A1").value = 0 then
Range("B9:I16").Select
ActiveSheet.Unprotect
else
msgbox "delete list"
end if
--
HTH...

Jim Thomlinson


"Savuti" wrote:

please help.... I need to have a macro run under the following criteria:

if cell A1 = 0 then run macro
if cell A1 0 then don't run macro and show message box to say "delete list"

below is the begining of the existing code ...... thanks

Range("B9:I16").Select
ActiveSheet.Unprotect
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default macro to run IF condition met
You can further reduce the code like this...

Sub Test()
If Range("A1").Value = 0 Then
ActiveSheet.Unprotect
Range("B9:I15").Copy Destination:=Range("B10")
Range("B7:I7").Copy Destination:=Range("B9")
Range("B7:I7").ClearContents
With Range("B9:I9")
.Locked = True
.FormulaHidden = False
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If
End Sub

Rick


"Alan" wrote in message
...
Another method which will limit the number of selections:

Sub Test()
If Range("A1").Value = 0 Then
Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Copy Destination:=Range("B16:I16")
Range("B14:I14").Copy Destination:=Range("B15:I15")
Range("B13:I13").Copy Destination:=Range("B14:I14")
Range("B12:I12").Copy Destination:=Range("B13:I13")
Range("B11:I11").Copy Destination:=Range("B12:I12")
Range("B10:I10").Copy Destination:=Range("B11:I11")
Range("B9:I9").Copy Destination:=Range("B10:I10")
Range("B7:I7").Copy Destination:=Range("B9:I9")
Range("B7:I7").ClearContents
With Range("B9:I9")
.Locked = True
.FormulaHidden = False
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If
End Sub

Alan



"Savuti" wrote:

Jim thanks for the reply. I did copy and paste your code, I got a Compile
error stating - Else without if.

I have included my complete existing code. Appreciate the help.

Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Select
Selection.Copy
Range("B16:I16").Select
ActiveSheet.Paste
Range("B14:I14").Select
Application.CutCopyMode = False
Selection.Copy
Range("B15:I15").Select
ActiveSheet.Paste
Range("B13:I13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B14:I14").Select
ActiveSheet.Paste
Range("B12:I12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B13:I13").Select
ActiveSheet.Paste
Range("B11:I11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B12:I12").Select
ActiveSheet.Paste
Range("B10:I10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B11:I11").Select
ActiveSheet.Paste
Range("B9:I9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B10:I10").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B9:I9").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B9:I9").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
End Sub

"Jim Thomlinson" wrote:

if range("A1").value = 0 then
Range("B9:I16").Select
ActiveSheet.Unprotect
else
msgbox "delete list"
end if
--
HTH...

Jim Thomlinson


"Savuti" wrote:

please help.... I need to have a macro run under the following
criteria:

if cell A1 = 0 then run macro
if cell A1 0 then don't run macro and show message box to say
"delete list"

below is the begining of the existing code ...... thanks

Range("B9:I16").Select
ActiveSheet.Unprotect

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default macro to run IF condition met
Rick thank you. I learned something new - much appreciated -Lord I have a lot
to learn

"Rick Rothstein (MVP - VB)" wrote:

You can further reduce the code like this...

Sub Test()
If Range("A1").Value = 0 Then
ActiveSheet.Unprotect
Range("B9:I15").Copy Destination:=Range("B10")
Range("B7:I7").Copy Destination:=Range("B9")
Range("B7:I7").ClearContents
With Range("B9:I9")
.Locked = True
.FormulaHidden = False
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If
End Sub

Rick


"Alan" wrote in message
...
Another method which will limit the number of selections:

Sub Test()
If Range("A1").Value = 0 Then
Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Copy Destination:=Range("B16:I16")
Range("B14:I14").Copy Destination:=Range("B15:I15")
Range("B13:I13").Copy Destination:=Range("B14:I14")
Range("B12:I12").Copy Destination:=Range("B13:I13")
Range("B11:I11").Copy Destination:=Range("B12:I12")
Range("B10:I10").Copy Destination:=Range("B11:I11")
Range("B9:I9").Copy Destination:=Range("B10:I10")
Range("B7:I7").Copy Destination:=Range("B9:I9")
Range("B7:I7").ClearContents
With Range("B9:I9")
.Locked = True
.FormulaHidden = False
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If
End Sub

Alan



"Savuti" wrote:

Jim thanks for the reply. I did copy and paste your code, I got a Compile
error stating - Else without if.

I have included my complete existing code. Appreciate the help.

Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Select
Selection.Copy
Range("B16:I16").Select
ActiveSheet.Paste
Range("B14:I14").Select
Application.CutCopyMode = False
Selection.Copy
Range("B15:I15").Select
ActiveSheet.Paste
Range("B13:I13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B14:I14").Select
ActiveSheet.Paste
Range("B12:I12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B13:I13").Select
ActiveSheet.Paste
Range("B11:I11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B12:I12").Select
ActiveSheet.Paste
Range("B10:I10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B11:I11").Select
ActiveSheet.Paste
Range("B9:I9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B10:I10").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B9:I9").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B9:I9").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
End Sub

"Jim Thomlinson" wrote:

if range("A1").value = 0 then
Range("B9:I16").Select
ActiveSheet.Unprotect
else
msgbox "delete list"
end if
--
HTH...

Jim Thomlinson


"Savuti" wrote:

please help.... I need to have a macro run under the following
criteria:

if cell A1 = 0 then run macro
if cell A1 0 then don't run macro and show message box to say
"delete list"

below is the begining of the existing code ...... thanks

Range("B9:I16").Select
ActiveSheet.Unprotect


Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 152
Default macro to run IF condition met
That's why you're the MVP! :) Thanks!

"Rick Rothstein (MVP - VB)" wrote:

You can further reduce the code like this...

Sub Test()
If Range("A1").Value = 0 Then
ActiveSheet.Unprotect
Range("B9:I15").Copy Destination:=Range("B10")
Range("B7:I7").Copy Destination:=Range("B9")
Range("B7:I7").ClearContents
With Range("B9:I9")
.Locked = True
.FormulaHidden = False
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If
End Sub

Rick


"Alan" wrote in message
...
Another method which will limit the number of selections:

Sub Test()
If Range("A1").Value = 0 Then
Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Copy Destination:=Range("B16:I16")
Range("B14:I14").Copy Destination:=Range("B15:I15")
Range("B13:I13").Copy Destination:=Range("B14:I14")
Range("B12:I12").Copy Destination:=Range("B13:I13")
Range("B11:I11").Copy Destination:=Range("B12:I12")
Range("B10:I10").Copy Destination:=Range("B11:I11")
Range("B9:I9").Copy Destination:=Range("B10:I10")
Range("B7:I7").Copy Destination:=Range("B9:I9")
Range("B7:I7").ClearContents
With Range("B9:I9")
.Locked = True
.FormulaHidden = False
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If
End Sub

Alan



"Savuti" wrote:

Jim thanks for the reply. I did copy and paste your code, I got a Compile
error stating - Else without if.

I have included my complete existing code. Appreciate the help.

Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Select
Selection.Copy
Range("B16:I16").Select
ActiveSheet.Paste
Range("B14:I14").Select
Application.CutCopyMode = False
Selection.Copy
Range("B15:I15").Select
ActiveSheet.Paste
Range("B13:I13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B14:I14").Select
ActiveSheet.Paste
Range("B12:I12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B13:I13").Select
ActiveSheet.Paste
Range("B11:I11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B12:I12").Select
ActiveSheet.Paste
Range("B10:I10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B11:I11").Select
ActiveSheet.Paste
Range("B9:I9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B10:I10").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B9:I9").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B9:I9").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
End Sub

"Jim Thomlinson" wrote:

if range("A1").value = 0 then
Range("B9:I16").Select
ActiveSheet.Unprotect
else
msgbox "delete list"
end if
--
HTH...

Jim Thomlinson


"Savuti" wrote:

please help.... I need to have a macro run under the following
criteria:

if cell A1 = 0 then run macro
if cell A1 0 then don't run macro and show message box to say
"delete list"

below is the begining of the existing code ...... thanks

Range("B9:I16").Select
ActiveSheet.Unprotect


Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default macro to run IF condition met
Alan, thank you as well for taking the time to respond. The more ways of
learning to do something the better.

"Alan" wrote:

That's why you're the MVP! :) Thanks!

"Rick Rothstein (MVP - VB)" wrote:

You can further reduce the code like this...

Sub Test()
If Range("A1").Value = 0 Then
ActiveSheet.Unprotect
Range("B9:I15").Copy Destination:=Range("B10")
Range("B7:I7").Copy Destination:=Range("B9")
Range("B7:I7").ClearContents
With Range("B9:I9")
.Locked = True
.FormulaHidden = False
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If
End Sub

Rick


"Alan" wrote in message
...
Another method which will limit the number of selections:

Sub Test()
If Range("A1").Value = 0 Then
Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Copy Destination:=Range("B16:I16")
Range("B14:I14").Copy Destination:=Range("B15:I15")
Range("B13:I13").Copy Destination:=Range("B14:I14")
Range("B12:I12").Copy Destination:=Range("B13:I13")
Range("B11:I11").Copy Destination:=Range("B12:I12")
Range("B10:I10").Copy Destination:=Range("B11:I11")
Range("B9:I9").Copy Destination:=Range("B10:I10")
Range("B7:I7").Copy Destination:=Range("B9:I9")
Range("B7:I7").ClearContents
With Range("B9:I9")
.Locked = True
.FormulaHidden = False
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If
End Sub

Alan



"Savuti" wrote:

Jim thanks for the reply. I did copy and paste your code, I got a Compile
error stating - Else without if.

I have included my complete existing code. Appreciate the help.

Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Select
Selection.Copy
Range("B16:I16").Select
ActiveSheet.Paste
Range("B14:I14").Select
Application.CutCopyMode = False
Selection.Copy
Range("B15:I15").Select
ActiveSheet.Paste
Range("B13:I13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B14:I14").Select
ActiveSheet.Paste
Range("B12:I12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B13:I13").Select
ActiveSheet.Paste
Range("B11:I11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B12:I12").Select
ActiveSheet.Paste
Range("B10:I10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B11:I11").Select
ActiveSheet.Paste
Range("B9:I9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B10:I10").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B9:I9").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B9:I9").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
End Sub

"Jim Thomlinson" wrote:

if range("A1").value = 0 then
Range("B9:I16").Select
ActiveSheet.Unprotect
else
msgbox "delete list"
end if
--
HTH...

Jim Thomlinson


"Savuti" wrote:

please help.... I need to have a macro run under the following
criteria:

if cell A1 = 0 then run macro
if cell A1 0 then don't run macro and show message box to say
"delete list"

below is the begining of the existing code ...... thanks

Range("B9:I16").Select
ActiveSheet.Unprotect


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
I need a macro to run on a set condition Savuti Excel Programming 3 May 1st 08 04:05 PM
use more than 4 condition in cf without using macro Montu Excel Worksheet Functions 3 November 15th 07 03:19 PM
Run Macro until a condition is met tx12345[_12_] Excel Programming 2 February 14th 06 01:02 AM
run macro only if condition is met Nikonov Excel Programming 2 November 8th 05 05:38 PM
macro to run only under certain condition - how? Orion[_2_] Excel Programming 2 December 22nd 04 10:08 AM


All times are GMT +1. The time now is 06:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement