| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
END IF in a macro
Hi,
I am working with Excel 2003 and have a problem with End If statement in a macro. I have created a workbook named Agency Billing. What I need to do is: -if the sum of range D2:D46 = 0.00, then go to sub-routine called CopyCosts -otherwise, copy specified range, open wb Bad Debt, find next blank row, paste data, save & close Bad Debt wb, then -go to sub-routine CopyCost -if cell named exp = 0.00, the go to sub-routine to CloseFile, otherwise -copy range named CopyCost, open wb Collection Cost, find next blank row, paste data, save and close Collection Cost wb, then -close active wb (Agency Billing) without saving. What happens is if D2:D46 is greater than 0.00, it will run this routine, but stops without going to the next sub-routine to CopyCosts. I have no idea why it will not run and am hoping someone can help. A portion of the macro I have is as follows: If Application.Sum(Worksheets("Bad Debt").Range("D2:D46")) = 0 Then Call CopyCost Else End If Dim lastRow As Long lastRow = Cells(Rows.Count, "l").End(xlUp).Row 'set variable to the last used row in L Range("H1:L" & lastRow).Copy Workbooks.Open Filename:="G:\Bad Debt" Range("A5").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Select Loop Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveCell.Offset(1, 0).Select ActiveWorkbook.Save ActiveWindow.Close End Sub Sub CopyCost() Application.Goto Reference:="exp" If Worksheets("Input").Range("exp") = 0 Then Call CloseFile End Else End If Application.Goto Reference:="CopyCost" Selection.Copy Workbooks.Open Filename:="G:\Collection Costs" Range("A5").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Select Loop Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveCell.Offset(1, 0).Select ActiveWorkbook.Save ActiveWindow.Close End Sub Sub CloseFile() Sheets("Input").Select Range("A1").Select MsgBox "Collection Costs & Bad Debt have been recorded. This file will close now" Application.ScreenUpdating = True ActiveWorkbook.Close SaveChanges:=False End Sub Thanks -- Linda |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
END IF in a macro
Maybe you are lookong for this:
Sub test() Dim lastRow As Long If Application.Sum(Worksheets("Bad Debt").Range("D2:D46")) < 0 Then lastRow = Cells(Rows.Count, "l").End(xlUp).Row 'set variable to the last used row in L Range("H1:L" & lastRow).Copy Workbooks.Open Filename:="G:\Bad Debt" Range("A5").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Select Loop Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveCell.Offset(1, 0).Select ActiveWorkbook.Save ActiveWindow.Close End If Call CopyCost End Sub Regards, Stefi mathel ezt *rta: Hi, I am working with Excel 2003 and have a problem with End If statement in a macro. I have created a workbook named Agency Billing. What I need to do is: -if the sum of range D2:D46 = 0.00, then go to sub-routine called CopyCosts -otherwise, copy specified range, open wb Bad Debt, find next blank row, paste data, save & close Bad Debt wb, then -go to sub-routine CopyCost -if cell named exp = 0.00, the go to sub-routine to CloseFile, otherwise -copy range named CopyCost, open wb Collection Cost, find next blank row, paste data, save and close Collection Cost wb, then -close active wb (Agency Billing) without saving. What happens is if D2:D46 is greater than 0.00, it will run this routine, but stops without going to the next sub-routine to CopyCosts. I have no idea why it will not run and am hoping someone can help. A portion of the macro I have is as follows: If Application.Sum(Worksheets("Bad Debt").Range("D2:D46")) = 0 Then Call CopyCost Else End If Dim lastRow As Long lastRow = Cells(Rows.Count, "l").End(xlUp).Row 'set variable to the last used row in L Range("H1:L" & lastRow).Copy Workbooks.Open Filename:="G:\Bad Debt" Range("A5").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Select Loop Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveCell.Offset(1, 0).Select ActiveWorkbook.Save ActiveWindow.Close End Sub Sub CopyCost() Application.Goto Reference:="exp" If Worksheets("Input").Range("exp") = 0 Then Call CloseFile End Else End If Application.Goto Reference:="CopyCost" Selection.Copy Workbooks.Open Filename:="G:\Collection Costs" Range("A5").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Select Loop Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveCell.Offset(1, 0).Select ActiveWorkbook.Save ActiveWindow.Close End Sub Sub CloseFile() Sheets("Input").Select Range("A1").Select MsgBox "Collection Costs & Bad Debt have been recorded. This file will close now" Application.ScreenUpdating = True ActiveWorkbook.Close SaveChanges:=False End Sub Thanks -- Linda |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
END IF in a macro
As suggested, I changed the line:
If Application.Sum(Worksheets("Bad Debt").Range("D2:D46")) = 0 Then..... to: If Application.Sum(Worksheets("Bad Debt").Range("D2:D46")) < 0 Then... It will now go to the Sub routine to CopyCost, and records the Cost, etc, however, it will not run the sub-routine to CloseFile. I tried changing the line: If Worksheets("Input").Range("exp") = 0 Then... to the same as above (If Worksheets("Input").Range("exp") < 0 Then.... The macro then goes to the last sub-routine CloseFile() without recording anything. Unfortunately, it is still not running properly. Thanks -- Linda "Stefi" wrote: Maybe you are lookong for this: Sub test() Dim lastRow As Long If Application.Sum(Worksheets("Bad Debt").Range("D2:D46")) < 0 Then lastRow = Cells(Rows.Count, "l").End(xlUp).Row 'set variable to the last used row in L Range("H1:L" & lastRow).Copy Workbooks.Open Filename:="G:\Bad Debt" Range("A5").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Select Loop Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveCell.Offset(1, 0).Select ActiveWorkbook.Save ActiveWindow.Close End If Call CopyCost End Sub Regards, Stefi mathel ezt *rta: Hi, I am working with Excel 2003 and have a problem with End If statement in a macro. I have created a workbook named Agency Billing. What I need to do is: -if the sum of range D2:D46 = 0.00, then go to sub-routine called CopyCosts -otherwise, copy specified range, open wb Bad Debt, find next blank row, paste data, save & close Bad Debt wb, then -go to sub-routine CopyCost -if cell named exp = 0.00, the go to sub-routine to CloseFile, otherwise -copy range named CopyCost, open wb Collection Cost, find next blank row, paste data, save and close Collection Cost wb, then -close active wb (Agency Billing) without saving. What happens is if D2:D46 is greater than 0.00, it will run this routine, but stops without going to the next sub-routine to CopyCosts. I have no idea why it will not run and am hoping someone can help. A portion of the macro I have is as follows: If Application.Sum(Worksheets("Bad Debt").Range("D2:D46")) = 0 Then Call CopyCost Else End If Dim lastRow As Long lastRow = Cells(Rows.Count, "l").End(xlUp).Row 'set variable to the last used row in L Range("H1:L" & lastRow).Copy Workbooks.Open Filename:="G:\Bad Debt" Range("A5").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Select Loop Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveCell.Offset(1, 0).Select ActiveWorkbook.Save ActiveWindow.Close End Sub Sub CopyCost() Application.Goto Reference:="exp" If Worksheets("Input").Range("exp") = 0 Then Call CloseFile End Else End If Application.Goto Reference:="CopyCost" Selection.Copy Workbooks.Open Filename:="G:\Collection Costs" Range("A5").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Select Loop Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveCell.Offset(1, 0).Select ActiveWorkbook.Save ActiveWindow.Close End Sub Sub CloseFile() Sheets("Input").Select Range("A1").Select MsgBox "Collection Costs & Bad Debt have been recorded. This file will close now" Application.ScreenUpdating = True ActiveWorkbook.Close SaveChanges:=False End Sub Thanks -- Linda |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
END IF in a macro
Without knowing exactly the job I can't review the whole program logiv, but
this part of sub CopyCost is ambiguous: Application.Goto Reference:="exp" If Worksheets("Input").Range("exp") = 0 Then Call CloseFile End Else End If Try this one instead: If Worksheets("Input").Range("exp") = 0 Then Call CloseFile End If Review your knowledge of using IF ... ELSE ... END IF structure! Regards, Stefi mathel ezt *rta: As suggested, I changed the line: If Application.Sum(Worksheets("Bad Debt").Range("D2:D46")) = 0 Then..... to: If Application.Sum(Worksheets("Bad Debt").Range("D2:D46")) < 0 Then... It will now go to the Sub routine to CopyCost, and records the Cost, etc, however, it will not run the sub-routine to CloseFile. I tried changing the line: If Worksheets("Input").Range("exp") = 0 Then... to the same as above (If Worksheets("Input").Range("exp") < 0 Then.... The macro then goes to the last sub-routine CloseFile() without recording anything. Unfortunately, it is still not running properly. Thanks -- Linda "Stefi" wrote: Maybe you are lookong for this: Sub test() Dim lastRow As Long If Application.Sum(Worksheets("Bad Debt").Range("D2:D46")) < 0 Then lastRow = Cells(Rows.Count, "l").End(xlUp).Row 'set variable to the last used row in L Range("H1:L" & lastRow).Copy Workbooks.Open Filename:="G:\Bad Debt" Range("A5").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Select Loop Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveCell.Offset(1, 0).Select ActiveWorkbook.Save ActiveWindow.Close End If Call CopyCost End Sub Regards, Stefi mathel ezt *rta: Hi, I am working with Excel 2003 and have a problem with End If statement in a macro. I have created a workbook named Agency Billing. What I need to do is: -if the sum of range D2:D46 = 0.00, then go to sub-routine called CopyCosts -otherwise, copy specified range, open wb Bad Debt, find next blank row, paste data, save & close Bad Debt wb, then -go to sub-routine CopyCost -if cell named exp = 0.00, the go to sub-routine to CloseFile, otherwise -copy range named CopyCost, open wb Collection Cost, find next blank row, paste data, save and close Collection Cost wb, then -close active wb (Agency Billing) without saving. What happens is if D2:D46 is greater than 0.00, it will run this routine, but stops without going to the next sub-routine to CopyCosts. I have no idea why it will not run and am hoping someone can help. A portion of the macro I have is as follows: If Application.Sum(Worksheets("Bad Debt").Range("D2:D46")) = 0 Then Call CopyCost Else End If Dim lastRow As Long lastRow = Cells(Rows.Count, "l").End(xlUp).Row 'set variable to the last used row in L Range("H1:L" & lastRow).Copy Workbooks.Open Filename:="G:\Bad Debt" Range("A5").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Select Loop Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveCell.Offset(1, 0).Select ActiveWorkbook.Save ActiveWindow.Close End Sub Sub CopyCost() Application.Goto Reference:="exp" If Worksheets("Input").Range("exp") = 0 Then Call CloseFile End Else End If Application.Goto Reference:="CopyCost" Selection.Copy Workbooks.Open Filename:="G:\Collection Costs" Range("A5").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Select Loop Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveCell.Offset(1, 0).Select ActiveWorkbook.Save ActiveWindow.Close End Sub Sub CloseFile() Sheets("Input").Select Range("A1").Select MsgBox "Collection Costs & Bad Debt have been recorded. This file will close now" Application.ScreenUpdating = True ActiveWorkbook.Close SaveChanges:=False End Sub Thanks -- Linda |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
END IF in a macro
Thank you for your help. You were right with regards to the Else, End If
statements. I removed or changed some of the statements, added to 'Call' for the next sub-routine, etc. and have the macro running as it should. I am relatively new to Excel and have never done programming, but I have learned a lot from this web site and find the assistance provided is invaluable! -- Linda "Stefi" wrote: Without knowing exactly the job I can't review the whole program logiv, but this part of sub CopyCost is ambiguous: Application.Goto Reference:="exp" If Worksheets("Input").Range("exp") = 0 Then Call CloseFile End Else End If Try this one instead: If Worksheets("Input").Range("exp") = 0 Then Call CloseFile End If Review your knowledge of using IF ... ELSE ... END IF structure! Regards, Stefi mathel ezt *rta: As suggested, I changed the line: If Application.Sum(Worksheets("Bad Debt").Range("D2:D46")) = 0 Then..... to: If Application.Sum(Worksheets("Bad Debt").Range("D2:D46")) < 0 Then... It will now go to the Sub routine to CopyCost, and records the Cost, etc, however, it will not run the sub-routine to CloseFile. I tried changing the line: If Worksheets("Input").Range("exp") = 0 Then... to the same as above (If Worksheets("Input").Range("exp") < 0 Then.... The macro then goes to the last sub-routine CloseFile() without recording anything. Unfortunately, it is still not running properly. Thanks -- Linda "Stefi" wrote: Maybe you are lookong for this: Sub test() Dim lastRow As Long If Application.Sum(Worksheets("Bad Debt").Range("D2:D46")) < 0 Then lastRow = Cells(Rows.Count, "l").End(xlUp).Row 'set variable to the last used row in L Range("H1:L" & lastRow).Copy Workbooks.Open Filename:="G:\Bad Debt" Range("A5").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Select Loop Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveCell.Offset(1, 0).Select ActiveWorkbook.Save ActiveWindow.Close End If Call CopyCost End Sub Regards, Stefi mathel ezt *rta: Hi, I am working with Excel 2003 and have a problem with End If statement in a macro. I have created a workbook named Agency Billing. What I need to do is: -if the sum of range D2:D46 = 0.00, then go to sub-routine called CopyCosts -otherwise, copy specified range, open wb Bad Debt, find next blank row, paste data, save & close Bad Debt wb, then -go to sub-routine CopyCost -if cell named exp = 0.00, the go to sub-routine to CloseFile, otherwise -copy range named CopyCost, open wb Collection Cost, find next blank row, paste data, save and close Collection Cost wb, then -close active wb (Agency Billing) without saving. What happens is if D2:D46 is greater than 0.00, it will run this routine, but stops without going to the next sub-routine to CopyCosts. I have no idea why it will not run and am hoping someone can help. A portion of the macro I have is as follows: If Application.Sum(Worksheets("Bad Debt").Range("D2:D46")) = 0 Then Call CopyCost Else End If Dim lastRow As Long lastRow = Cells(Rows.Count, "l").End(xlUp).Row 'set variable to the last used row in L Range("H1:L" & lastRow).Copy Workbooks.Open Filename:="G:\Bad Debt" Range("A5").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Select Loop Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveCell.Offset(1, 0).Select ActiveWorkbook.Save ActiveWindow.Close End Sub Sub CopyCost() Application.Goto Reference:="exp" If Worksheets("Input").Range("exp") = 0 Then Call CloseFile End Else End If Application.Goto Reference:="CopyCost" Selection.Copy Workbooks.Open Filename:="G:\Collection Costs" Range("A5").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Select Loop Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveCell.Offset(1, 0).Select ActiveWorkbook.Save ActiveWindow.Close End Sub Sub CloseFile() Sheets("Input").Select Range("A1").Select MsgBox "Collection Costs & Bad Debt have been recorded. This file will close now" Application.ScreenUpdating = True ActiveWorkbook.Close SaveChanges:=False End Sub Thanks -- Linda |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
END IF in a macro
You are welcome! Thanks for the feedback!
Stefi mathel ezt *rta: Thank you for your help. You were right with regards to the Else, End If statements. I removed or changed some of the statements, added to 'Call' for the next sub-routine, etc. and have the macro running as it should. I am relatively new to Excel and have never done programming, but I have learned a lot from this web site and find the assistance provided is invaluable! -- Linda "Stefi" wrote: Without knowing exactly the job I can't review the whole program logiv, but this part of sub CopyCost is ambiguous: Application.Goto Reference:="exp" If Worksheets("Input").Range("exp") = 0 Then Call CloseFile End Else End If Try this one instead: If Worksheets("Input").Range("exp") = 0 Then Call CloseFile End If Review your knowledge of using IF ... ELSE ... END IF structure! Regards, Stefi mathel ezt *rta: As suggested, I changed the line: If Application.Sum(Worksheets("Bad Debt").Range("D2:D46")) = 0 Then..... to: If Application.Sum(Worksheets("Bad Debt").Range("D2:D46")) < 0 Then... It will now go to the Sub routine to CopyCost, and records the Cost, etc, however, it will not run the sub-routine to CloseFile. I tried changing the line: If Worksheets("Input").Range("exp") = 0 Then... to the same as above (If Worksheets("Input").Range("exp") < 0 Then.... The macro then goes to the last sub-routine CloseFile() without recording anything. Unfortunately, it is still not running properly. Thanks -- Linda "Stefi" wrote: Maybe you are lookong for this: Sub test() Dim lastRow As Long If Application.Sum(Worksheets("Bad Debt").Range("D2:D46")) < 0 Then lastRow = Cells(Rows.Count, "l").End(xlUp).Row 'set variable to the last used row in L Range("H1:L" & lastRow).Copy Workbooks.Open Filename:="G:\Bad Debt" Range("A5").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Select Loop Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveCell.Offset(1, 0).Select ActiveWorkbook.Save ActiveWindow.Close End If Call CopyCost End Sub Regards, Stefi mathel ezt *rta: Hi, I am working with Excel 2003 and have a problem with End If statement in a macro. I have created a workbook named Agency Billing. What I need to do is: -if the sum of range D2:D46 = 0.00, then go to sub-routine called CopyCosts -otherwise, copy specified range, open wb Bad Debt, find next blank row, paste data, save & close Bad Debt wb, then -go to sub-routine CopyCost -if cell named exp = 0.00, the go to sub-routine to CloseFile, otherwise -copy range named CopyCost, open wb Collection Cost, find next blank row, paste data, save and close Collection Cost wb, then -close active wb (Agency Billing) without saving. What happens is if D2:D46 is greater than 0.00, it will run this routine, but stops without going to the next sub-routine to CopyCosts. I have no idea why it will not run and am hoping someone can help. A portion of the macro I have is as follows: If Application.Sum(Worksheets("Bad Debt").Range("D2:D46")) = 0 Then Call CopyCost Else End If Dim lastRow As Long lastRow = Cells(Rows.Count, "l").End(xlUp).Row 'set variable to the last used row in L Range("H1:L" & lastRow).Copy Workbooks.Open Filename:="G:\Bad Debt" Range("A5").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Select Loop Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveCell.Offset(1, 0).Select ActiveWorkbook.Save ActiveWindow.Close End Sub Sub CopyCost() Application.Goto Reference:="exp" If Worksheets("Input").Range("exp") = 0 Then Call CloseFile End Else End If Application.Goto Reference:="CopyCost" Selection.Copy Workbooks.Open Filename:="G:\Collection Costs" Range("A5").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Select Loop Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveCell.Offset(1, 0).Select ActiveWorkbook.Save ActiveWindow.Close End Sub Sub CloseFile() Sheets("Input").Select Range("A1").Select MsgBox "Collection Costs & Bad Debt have been recorded. This file will close now" Application.ScreenUpdating = True ActiveWorkbook.Close SaveChanges:=False End Sub Thanks -- Linda |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
| Macro to copy and paste values (columns)I have a macro file built | Excel Programming | |||
| Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
| macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
| Start Macro / Stop Macro / Restart Macro | Excel Programming | |||
Linear Mode
