Stop after IF Then

bgkgmg · Sep 27, 2008

I'm running macro with many If Then statements. If Then applies to matching
invoice numbers and then moving info to row on another sheet. I am locking
cells that are involved on sheet2 and protecting worksheet after each one. I
would like macro to stop running after whichever If Then statement applies
first so other rows of If Then statements are not locked thus allowing me to
enter next statement.
I hope you understand question.
Thanks
Bobby

Mike H · Sep 27, 2008

Hi,

I think you need to explain a bit more clearly and post your code describing
the conditions on which you want to exit.

Mike

Rick Rothstein · Sep 27, 2008

As Mike said, it is hard to tell exactly what you doing without seeing your
code. However, when you say you want to stop "after whichever If Then
statement applies first" sounds like your code is looping down a column of
invoice numbers looking for a "current" invoice number and, once found, you
take action and then want to stop. If that is what you are doing, I think
you can eliminate the loop altogether and use the Find method (of a range)
to locate your "match", and then just do whatever you are doing to that
found reference.

Ron Luzius · Sep 27, 2008

You may want to also look into "Select Case" instead of a lot of IFs

bgkgmg · Sep 28, 2008

Sub Macro1()
'
' Macro1 Macro

If Worksheets("Enter").Range("AM4").Value =
Worksheets("Daily").Range("B4") Then
Worksheets("Daily").Range("C4").Value =
Worksheets("Enter").Range("AA9").Value
Worksheets("Daily").Range("D4").Value =
Worksheets("Enter").Range("AG6").Value
Worksheets("Daily").Range("E4").Value =
Worksheets("Enter").Range("AA11").Value
Worksheets("Daily").Range("G4").Value =
Worksheets("Enter").Range("AA15").Value
Worksheets("Enter").Range("AA9").Value = ""
Worksheets("Enter").Range("AG6").Value = ""
Worksheets("Enter").Range("AA11").Value = ""
Worksheets("Enter").Range("AA15").Value = ""
Sheets("Daily").Select
Range("C4,D4,E4,G4").Select
ActiveSheet.Unprotect
Sheets("Daily").Select
Range("C4,D4,E4,G4").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End If

If Worksheets("Enter").Range("AM4").Value =
Worksheets("Daily").Range("B5") Then
Worksheets("Daily").Range("C5").Value =
Worksheets("Enter").Range("AA9").Value
Worksheets("Daily").Range("D5").Value =
Worksheets("Enter").Range("AG6").Value
Worksheets("Daily").Range("E5").Value =
Worksheets("Enter").Range("AA11").Value
Worksheets("Daily").Range("G5").Value =
Worksheets("Enter").Range("AA15").Value
Worksheets("Enter").Range("AA9").Value = ""
Worksheets("Enter").Range("AG6").Value = ""
Worksheets("Enter").Range("AA11").Value = ""
Worksheets("Enter").Range("AA15").Value = ""
Sheets("Daily").Select
Range("C5,D5,E5,G5").Select
ActiveSheet.Unprotect
Sheets("Daily").Select
Range("C5,D5,E5,G5").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End If
If Worksheets("Daily").Range("D5").Value = "" Then
Worksheets("Enter").Range("AM4").Value = "102"
End If
etc...

bgkgmg · Sep 28, 2008

Sub Macro1()
'
' Macro1 Macro

If Worksheets("Enter").Range("AM4").Value =
Worksheets("Daily").Range("B4") Then
Worksheets("Daily").Range("C4").Value =
Worksheets("Enter").Range("AA9").Value
Worksheets("Daily").Range("D4").Value =
Worksheets("Enter").Range("AG6").Value
Worksheets("Daily").Range("E4").Value =
Worksheets("Enter").Range("AA11").Value
Worksheets("Daily").Range("G4").Value =
Worksheets("Enter").Range("AA15").Value
Worksheets("Enter").Range("AA9").Value = ""
Worksheets("Enter").Range("AG6").Value = ""
Worksheets("Enter").Range("AA11").Value = ""
Worksheets("Enter").Range("AA15").Value = ""
Sheets("Daily").Select
Range("C4,D4,E4,G4").Select
ActiveSheet.Unprotect
Sheets("Daily").Select
Range("C4,D4,E4,G4").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End If

If Worksheets("Enter").Range("AM4").Value =
Worksheets("Daily").Range("B5") Then
Worksheets("Daily").Range("C5").Value =
Worksheets("Enter").Range("AA9").Value
Worksheets("Daily").Range("D5").Value =
Worksheets("Enter").Range("AG6").Value
Worksheets("Daily").Range("E5").Value =
Worksheets("Enter").Range("AA11").Value
Worksheets("Daily").Range("G5").Value =
Worksheets("Enter").Range("AA15").Value
Worksheets("Enter").Range("AA9").Value = ""
Worksheets("Enter").Range("AG6").Value = ""
Worksheets("Enter").Range("AA11").Value = ""
Worksheets("Enter").Range("AA15").Value = ""
Sheets("Daily").Select
Range("C5,D5,E5,G5").Select
ActiveSheet.Unprotect
Sheets("Daily").Select
Range("C5,D5,E5,G5").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End If
If Worksheets("Daily").Range("D5").Value = "" Then
Worksheets("Enter").Range("AM4").Value = "102"
End If
etc..

Don Guillett · Sep 28, 2008

You need to explain this FIRST a bit further.

stop running after whichever If Then statement applies
first so other rows of If Then statements are not locked thus allowing me
to

bgkgmg · Sep 28, 2008

Invoice number in sheet(Enter)cell AM4. When i run macro the values I
assigned in invoice are copied to Sheet(Daily) in same row as corresponding
invoice number. After each invoice is copied I protected the sheet(Daily).
I want the invoice number in sheet(Enter) to increase in value by 1 after
macro is run. The current formula below does that but when I go to enter
next invoice the sheet is protected which will not allow me to copy data. I
think when invoice number is changed to 102, or next invoice, the macro
continues and protects sheet.

Sub Macro1()
'Macro1 Macro

If Worksheets("Enter").Range("AM4").Value =
Worksheets("Daily").Range("B4") Then
Worksheets("Daily").Range("C4").Value =
Worksheets("Enter").Range("AA9").Value
Worksheets("Daily").Range("D4").Value =
Worksheets("Enter").Range("AG6").Value
Worksheets("Daily").Range("E4").Value =
Worksheets("Enter").Range("AA11").Value
Worksheets("Daily").Range("G4").Value =
Worksheets("Enter").Range("AA15").Value
Worksheets("Enter").Range("AA9").Value = ""
Worksheets("Enter").Range("AG6").Value = ""
Worksheets("Enter").Range("AA11").Value = ""
Worksheets("Enter").Range("AA15").Value = ""
Sheets("Daily").Select
Range("C4,D4,E4,G4").Select
ActiveSheet.Unprotect
Sheets("Daily").Select
Range("C4,D4,E4,G4").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If

If Worksheets("Enter").Range("AM4").Value =
Worksheets("Daily").Range("B5") Then
Worksheets("Daily").Range("C5").Value =
Worksheets("Enter").Range("AA9").Value
Worksheets("Daily").Range("D5").Value =
Worksheets("Enter").Range("AG6").Value
Worksheets("Daily").Range("E5").Value =
Worksheets("Enter").Range("AA11").Value
Worksheets("Daily").Range("G5").Value =
Worksheets("Enter").Range("AA15").Value
Worksheets("Enter").Range("AA9").Value = ""
Worksheets("Enter").Range("AG6").Value = ""
Worksheets("Enter").Range("AA11").Value = ""
Worksheets("Enter").Range("AA15").Value = ""
Sheets("Daily").Select
Range("C5,D5,E5,G5").Select
ActiveSheet.Unprotect
Sheets("Daily").Select
Range("C5,D5,E5,G5").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If

If Worksheets("Daily").Range("D5").Value = "" Then
Worksheets("Enter").Range("AM4").Value = "102"
End If
etc..

Rick Rothstein · Sep 28, 2008

I think part of the problem those who have responded so far are having is
that you are expecting us to know more about your process than you have a
right to assume. For me, I would be interested in a description of, but not
your code (mainly because I'm afraid of how long it might be), what "etc."
means. I see two large If..Then blocks of code followed by a short If..Then
block of code.... how many more times does your "etc." mean for them to
repeat? Also, you are hard-coding the setting for AM4 on the Enter worksheet
to 102 in that short If..Then block, but this is the same cell you check for
initially. What if that initial value is 103? Do you then step back to 102?
And if so, what happens in the "etc." part... does the next short If..Then
block hard code AM4 to 103 again (what I proposed you started with)? I am
really confused, not with your code, but rather with what it going on in
your macro (that is, the process it is supposed to be carrying out for you).
Personally, I think (based on a guess of what I think you might be doing)
that you have way, way too much code. I really believe a simple Find method
call followed by some settings may be all you need; however, I am confused
enough about what you are doing that I can't know for sure. If you could
find a way to describe the process you are attempting to implement in words,
that would probably be useful.

bgkgmg · Sep 28, 2008

Invoice number in sheet(Enter)cell AM4. When i run macro the values I
assigned in invoice are copied to Sheet(Daily) in same row as corresponding
invoice number. After each invoice is copied I protected the sheet(Daily).
I want the invoice number in sheet(Enter) to increase in value by 1 after
macro is run. The current formula below does that but when I go to enter
next invoice the sheet is protected which will not allow me to copy data. I
think when invoice number is changed to 102, or next invoice, the macro
continues and protects sheet.

Sub Macro1()
'Macro1 Macro

If Worksheets("Enter").Range("AM4").Value =
Worksheets("Daily").Range("B4") Then
Worksheets("Daily").Range("C4").Value =
Worksheets("Enter").Range("AA9").Value
Worksheets("Daily").Range("D4").Value =
Worksheets("Enter").Range("AG6").Value
Worksheets("Daily").Range("E4").Value =
Worksheets("Enter").Range("AA11").Value
Worksheets("Daily").Range("G4").Value =
Worksheets("Enter").Range("AA15").Value
Worksheets("Enter").Range("AA9").Value = ""
Worksheets("Enter").Range("AG6").Value = ""
Worksheets("Enter").Range("AA11").Value = ""
Worksheets("Enter").Range("AA15").Value = ""
Sheets("Daily").Select
Range("C4,D4,E4,G4").Select
ActiveSheet.Unprotect
Sheets("Daily").Select
Range("C4,D4,E4,G4").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If

If Worksheets("Enter").Range("AM4").Value =
Worksheets("Daily").Range("B5") Then
Worksheets("Daily").Range("C5").Value =
Worksheets("Enter").Range("AA9").Value
Worksheets("Daily").Range("D5").Value =
Worksheets("Enter").Range("AG6").Value
Worksheets("Daily").Range("E5").Value =
Worksheets("Enter").Range("AA11").Value
Worksheets("Daily").Range("G5").Value =
Worksheets("Enter").Range("AA15").Value
Worksheets("Enter").Range("AA9").Value = ""
Worksheets("Enter").Range("AG6").Value = ""
Worksheets("Enter").Range("AA11").Value = ""
Worksheets("Enter").Range("AA15").Value = ""
Sheets("Daily").Select
Range("C5,D5,E5,G5").Select
ActiveSheet.Unprotect
Sheets("Daily").Select
Range("C5,D5,E5,G5").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If

If Worksheets("Daily").Range("D5").Value = "" Then
Worksheets("Enter").Range("AM4").Value = "102"
End If
etc..

bgkgmg · Sep 28, 2008

Invoice number in sheet(Enter)cell AM4. When i run macro the values I
assigned in invoice are copied to Sheet(Daily) in same row as corresponding
invoice number. After each invoice is copied I protected the sheet(Daily).
I want the invoice number in sheet(Enter) to increase in value by 1 after
macro is run. The current formula below does that but when I go to enter
next invoice the sheet is protected which will not allow me to copy data. I
think when invoice number is changed to 102, or next invoice, the macro
continues and protects sheet.

Sub Macro1()
'Macro1 Macro

If Worksheets("Enter").Range("AM4").Value =
Worksheets("Daily").Range("B4") Then
Worksheets("Daily").Range("C4").Value =
Worksheets("Enter").Range("AA9").Value
Worksheets("Daily").Range("D4").Value =
Worksheets("Enter").Range("AG6").Value
Worksheets("Daily").Range("E4").Value =
Worksheets("Enter").Range("AA11").Value
Worksheets("Daily").Range("G4").Value =
Worksheets("Enter").Range("AA15").Value
Worksheets("Enter").Range("AA9").Value = ""
Worksheets("Enter").Range("AG6").Value = ""
Worksheets("Enter").Range("AA11").Value = ""
Worksheets("Enter").Range("AA15").Value = ""
Sheets("Daily").Select
Range("C4,D4,E4,G4").Select
ActiveSheet.Unprotect
Sheets("Daily").Select
Range("C4,D4,E4,G4").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If

If Worksheets("Enter").Range("AM4").Value =
Worksheets("Daily").Range("B5") Then
Worksheets("Daily").Range("C5").Value =
Worksheets("Enter").Range("AA9").Value
Worksheets("Daily").Range("D5").Value =
Worksheets("Enter").Range("AG6").Value
Worksheets("Daily").Range("E5").Value =
Worksheets("Enter").Range("AA11").Value
Worksheets("Daily").Range("G5").Value =
Worksheets("Enter").Range("AA15").Value
Worksheets("Enter").Range("AA9").Value = ""
Worksheets("Enter").Range("AG6").Value = ""
Worksheets("Enter").Range("AA11").Value = ""
Worksheets("Enter").Range("AA15").Value = ""
Sheets("Daily").Select
Range("C5,D5,E5,G5").Select
ActiveSheet.Unprotect
Sheets("Daily").Select
Range("C5,D5,E5,G5").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If

If Worksheets("Daily").Range("D5").Value = "" Then
Worksheets("Enter").Range("AM4").Value = "102"
End If
etc..

Don Guillett · Sep 28, 2008

If desired, send your workbook to my address below along with snippets of
these emails from excel.programming so I will know what this is about. Also
a clear detailed explanation on a newly inserted worksheet of exactly what
you expect. I will try to take a look, time permitting.

It is very important for you to explain what you mean by FIRST IF and then
exit sub.

Stop after IF Then

bgkgmg

Mike H

Rick Rothstein

Ron Luzius

bgkgmg

bgkgmg

Don Guillett

bgkgmg

Rick Rothstein

bgkgmg

bgkgmg

Don Guillett