Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Starting Auto_Close macro based on an IF statement
I'm over my head --
I run an auto_close macro to retrieve and save data collected during a user session. The macro takes all rows with data, copies them to a folder, then clears the collection sheet for next session. If there is NO DATA collected, I want to end the macro right away. Otherwise, I'm getting an error saying Excel can't move things off the worksheet because data may be lost . . . to paraphrase I think it's a runtime error 1004? Here is what I came up w/, but not sure if it's even close -- it doesn't work though. If Sheets("Worksheet") & Range("A3") = "" Then ActiveWorkbook.Save ActiveWorkbook.Close End If Else Workbooks.Open Filename:="W:\. . . Where if A# < "", then I want to run the macro. Any help? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Starting Auto_Close macro based on an IF statement
hi
you have.... If Sheets("Worksheet") & Range("A3") = "" Then that should be... If Sheets("Worksheet").Range("A3") = "" Then ie replace & with . also loose the end if after activeworkbook.close i see nothing else wrong in theory but i haven't tested. you are on the right track. just a syntax thing. Regards FSt1 "questor" wrote: I'm over my head -- I run an auto_close macro to retrieve and save data collected during a user session. The macro takes all rows with data, copies them to a folder, then clears the collection sheet for next session. If there is NO DATA collected, I want to end the macro right away. Otherwise, I'm getting an error saying Excel can't move things off the worksheet because data may be lost . . . to paraphrase I think it's a runtime error 1004? Here is what I came up w/, but not sure if it's even close -- it doesn't work though. If Sheets("Worksheet") & Range("A3") = "" Then ActiveWorkbook.Save ActiveWorkbook.Close End If Else Workbooks.Open Filename:="W:\. . . Where if A# < "", then I want to run the macro. Any help? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Starting Auto_Close macro based on an IF statement
I get a VB Compile Error: "Block If without End If"
Since I tried this with A3 being blank, I figured it would end quick (save & close) w/out running the rest of the macro -- which I want to run if A3 isn't blank. I think that's why I stuck the "End If" in there to begin with. Any ideas on getting around the compile error? You do think I'm on the right track tho -- basically conditionally running a macro or just closing if ""? Didn't know if I should b on a completely different track or not. Thanks. "FSt1" wrote: hi you have.... If Sheets("Worksheet") & Range("A3") = "" Then that should be... If Sheets("Worksheet").Range("A3") = "" Then ie replace & with . also loose the end if after activeworkbook.close i see nothing else wrong in theory but i haven't tested. you are on the right track. just a syntax thing. Regards FSt1 "questor" wrote: I'm over my head -- I run an auto_close macro to retrieve and save data collected during a user session. The macro takes all rows with data, copies them to a folder, then clears the collection sheet for next session. If there is NO DATA collected, I want to end the macro right away. Otherwise, I'm getting an error saying Excel can't move things off the worksheet because data may be lost . . . to paraphrase I think it's a runtime error 1004? Here is what I came up w/, but not sure if it's even close -- it doesn't work though. If Sheets("Worksheet") & Range("A3") = "" Then ActiveWorkbook.Save ActiveWorkbook.Close End If Else Workbooks.Open Filename:="W:\. . . Where if A# < "", then I want to run the macro. Any help? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Starting Auto_Close macro based on an IF statement
If Sheets("Worksheet").Range("A3").value = "" Then
ActiveWorkbook.Save ActiveWorkbook.Close Else Workbooks.Open Filename:="W:\. . . If this doesn't help, it's time to post your current code. questor wrote: I get a VB Compile Error: "Block If without End If" Since I tried this with A3 being blank, I figured it would end quick (save & close) w/out running the rest of the macro -- which I want to run if A3 isn't blank. I think that's why I stuck the "End If" in there to begin with. Any ideas on getting around the compile error? You do think I'm on the right track tho -- basically conditionally running a macro or just closing if ""? Didn't know if I should b on a completely different track or not. Thanks. "FSt1" wrote: hi you have.... If Sheets("Worksheet") & Range("A3") = "" Then that should be... If Sheets("Worksheet").Range("A3") = "" Then ie replace & with . also loose the end if after activeworkbook.close i see nothing else wrong in theory but i haven't tested. you are on the right track. just a syntax thing. Regards FSt1 "questor" wrote: I'm over my head -- I run an auto_close macro to retrieve and save data collected during a user session. The macro takes all rows with data, copies them to a folder, then clears the collection sheet for next session. If there is NO DATA collected, I want to end the macro right away. Otherwise, I'm getting an error saying Excel can't move things off the worksheet because data may be lost . . . to paraphrase I think it's a runtime error 1004? Here is what I came up w/, but not sure if it's even close -- it doesn't work though. If Sheets("Worksheet") & Range("A3") = "" Then ActiveWorkbook.Save ActiveWorkbook.Close End If Else Workbooks.Open Filename:="W:\. . . Where if A# < "", then I want to run the macro. Any help? -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Starting Auto_Close macro based on an IF statement
Kept getting the compile error. I inserted "End if" on the 2nd to last line
just above "End Sub" and it seemed to do the trick / close the statement?. Sorry to say I really don't understand much of this stuff (and my code is a cluged cluster of recordings and written commands). I'm just happy when it works. So far, it looks like it's doing just what I want. Still learning . . .. Thanks & Best Regards ~ "Questor" "Dave Peterson" wrote: If Sheets("Worksheet").Range("A3").value = "" Then ActiveWorkbook.Save ActiveWorkbook.Close Else Workbooks.Open Filename:="W:\. . . If this doesn't help, it's time to post your current code. questor wrote: I get a VB Compile Error: "Block If without End If" Since I tried this with A3 being blank, I figured it would end quick (save & close) w/out running the rest of the macro -- which I want to run if A3 isn't blank. I think that's why I stuck the "End If" in there to begin with. Any ideas on getting around the compile error? You do think I'm on the right track tho -- basically conditionally running a macro or just closing if ""? Didn't know if I should b on a completely different track or not. Thanks. "FSt1" wrote: hi you have.... If Sheets("Worksheet") & Range("A3") = "" Then that should be... If Sheets("Worksheet").Range("A3") = "" Then ie replace & with . also loose the end if after activeworkbook.close i see nothing else wrong in theory but i haven't tested. you are on the right track. just a syntax thing. Regards FSt1 "questor" wrote: I'm over my head -- I run an auto_close macro to retrieve and save data collected during a user session. The macro takes all rows with data, copies them to a folder, then clears the collection sheet for next session. If there is NO DATA collected, I want to end the macro right away. Otherwise, I'm getting an error saying Excel can't move things off the worksheet because data may be lost . . . to paraphrase I think it's a runtime error 1004? Here is what I came up w/, but not sure if it's even close -- it doesn't work though. If Sheets("Worksheet") & Range("A3") = "" Then ActiveWorkbook.Save ActiveWorkbook.Close End If Else Workbooks.Open Filename:="W:\. . . Where if A# < "", then I want to run the macro. Any help? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Starting A Macro | Excel Discussion (Misc queries) | |||
starting a macro at startup | Excel Discussion (Misc queries) | |||
Starting a macro with the IF function | Excel Worksheet Functions | |||
How can I assign a range starting cell based on a variable locati. | Excel Discussion (Misc queries) | |||
How can I assign a range starting cell based on a variable locati. | Excel Worksheet Functions |