| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop after IF Then
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Stop after IF Then
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 "bgkgmg" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Stop after IF Then
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. -- Rick (MVP - Excel) "bgkgmg" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Stop after IF Then
You may want to also look into "Select Case" instead of a lot of IFs
|
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Stop after IF Then
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... "Mike H" wrote: 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 "bgkgmg" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Stop after IF Then
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" wrote: 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. -- Rick (MVP - Excel) "bgkgmg" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Stop after IF Then
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "bgkgmg" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Stop after IF Then
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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "bgkgmg" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Stop after IF Then
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. -- Rick (MVP - Excel) "bgkgmg" wrote in message ... 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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "bgkgmg" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Stop after IF Then
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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "bgkgmg" wrote in message ... 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" wrote: 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 "bgkgmg" wrote: 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Stop after IF Then
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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "bgkgmg" wrote in message ... 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 "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "bgkgmg" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How stop the "started office live" pop-up to stop. it is checked | Excel Discussion (Misc queries) | |||
| Stop (C) becoming © | Excel Discussion (Misc queries) | |||
| How do I stop other circles in other cells to stop selecting? | Excel Worksheet Functions | |||
| How do I stop other circles in other boxes to stop selecting? | Excel Worksheet Functions | |||
| Macro: With Stop it works. Without Stop it doesn't. | Excel Programming | |||
Linear Mode
