Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Skip function if data sheet is empty
Hi,
I have macro to extract data and then I use the information to built graph and other things. The problem is when the data2 worksheet is empty (like if that day I didn' run the machine) the program goes all the way to the end but when I try to run it again I get an error message. I would like to add a if statement (or somehting else) to look in the worksheet data2 before it proceed with the rest of the programming and if it's empty go to the end of the program and give me an empty worksheet message. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Skip function if data sheet is empty
Just need to use CountA to count the unempty cells in the data2
sheet. Something like: Dim ws As Worksheet Set ws = Worksheets("data2") If WorksheetFunction.CountA(ws.Cells) = 0 Then MsgBox "Worksheet '" & ws.Name & _ "' is empty. Can not proceed", , "Error" End End If Of course, there are other ways of accomplishing this as well. Hope this helps. answer33 wrote: Hi, I have macro to extract data and then I use the information to built graph and other things. The problem is when the data2 worksheet is empty (like if that day I didn' run the machine) the program goes all the way to the end but when I try to run it again I get an error message. I would like to add a if statement (or somehting else) to look in the worksheet data2 before it proceed with the rest of the programming and if it's empty go to the end of the program and give me an empty worksheet message. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Skip function if data sheet is empty
test with this
Sub test() If Range("A1") = "" Then MsgBox "Worksheet is empty" Else MsgBox "Put Your Code Here" End If End Sub "answer33" wrote: Hi, I have macro to extract data and then I use the information to built graph and other things. The problem is when the data2 worksheet is empty (like if that day I didn' run the machine) the program goes all the way to the end but when I try to run it again I get an error message. I would like to add a if statement (or somehting else) to look in the worksheet data2 before it proceed with the rest of the programming and if it's empty go to the end of the program and give me an empty worksheet message. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Skip function if data sheet is empty
Opps try this
Sub test() If Range("A1") = "" Then MsgBox "Worksheet is empty" Exit sub Else MsgBox "Put Your Code Here" End If End Sub "answer33" wrote: Hi, I have macro to extract data and then I use the information to built graph and other things. The problem is when the data2 worksheet is empty (like if that day I didn' run the machine) the program goes all the way to the end but when I try to run it again I get an error message. I would like to add a if statement (or somehting else) to look in the worksheet data2 before it proceed with the rest of the programming and if it's empty go to the end of the program and give me an empty worksheet message. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Skip function if data sheet is empty
That would only check to see if cell A1 is empty. That doesn't
necessarily guarantee that the whole sheet is. Mike wrote: Opps try this Sub test() If Range("A1") = "" Then MsgBox "Worksheet is empty" Exit sub Else MsgBox "Put Your Code Here" End If End Sub "answer33" wrote: Hi, I have macro to extract data and then I use the information to built graph and other things. The problem is when the data2 worksheet is empty (like if that day I didn' run the machine) the program goes all the way to the end but when I try to run it again I get an error message. I would like to add a if statement (or somehting else) to look in the worksheet data2 before it proceed with the rest of the programming and if it's empty go to the end of the program and give me an empty worksheet message. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Skip function if data sheet is empty
I like the suggestion but I also need that program to run in loop so if it's
empty I'll recorded it empty and go to next file. Here the code i have. I need to check after Get_Data_from_SQL. If the Data2 worksheet is empty then go after the Graph sub program and complete the task. Thanks, S/bastien Sub Program() Dim t1 As Single 'Reset worksheets, clean cells Reset 'This will remove screen refresh! Excel.Application.ScreenUpdating = False 'This will start the timer and save it in cell C1. starttime = Timer Sheets("Data").Range("C1") = starttime Sheets("Data").Range("C3") = "=R[-1]C-R[-2]C" 'Load file Load_file Excel.Application.ScreenUpdating = False 'This is to mark the whole data Mark_data 'This will set the number of cycle Nr_of_cycles 'This extract data from SQL Get_Data_from_SQL 'This creates the summary table Create_summary_table 'This will give the lenght of the jumbo Set_lenght 'this will give the slitters slitters 'This will give the winding of the jumbo winding 'This will give the set numset 'This will give the set numberset 'This will give the set numsetrep 'This will give roll lenght extract_MD 'This will give defects info on each roll roll 'This will erase the trim Deleterows 'This creates the graph Graph_inputs Graph 'This will end the timer endtime = Timer Sheets("Data").Range("C2") = endtime t1 = Sheets("Data").Range("C3") Sheets("Data").Select Range("B124", "B65536").Select Selection.ClearContents Sheets("Front").Select Range("D9").Select 'This will start screen refresh Excel.Application.ScreenUpdating = True 'This is the mmessage box to report calculation time. MsgBox "Time of analysis: " & Chr(9) & t1 & " sec" End Sub "Mike" wrote: Opps try this Sub test() If Range("A1") = "" Then MsgBox "Worksheet is empty" Exit sub Else MsgBox "Put Your Code Here" End If End Sub "answer33" wrote: Hi, I have macro to extract data and then I use the information to built graph and other things. The problem is when the data2 worksheet is empty (like if that day I didn' run the machine) the program goes all the way to the end but when I try to run it again I get an error message. I would like to add a if statement (or somehting else) to look in the worksheet data2 before it proceed with the rest of the programming and if it's empty go to the end of the program and give me an empty worksheet message. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Skip function if data sheet is empty
Just place a label after the Graph sub program and use a GoTo
statement to go there if data2 is empty. Dim ws As Worksheet Set ws = Worksheets("data2") If WorksheetFunction.CountA(ws.Cells) = 0 Then GoTo labalNameHere answer33 wrote: I like the suggestion but I also need that program to run in loop so if it's empty I'll recorded it empty and go to next file. Here the code i have. I need to check after Get_Data_from_SQL. If the Data2 worksheet is empty then go after the Graph sub program and complete the task. Thanks, S/bastien Sub Program() Dim t1 As Single 'Reset worksheets, clean cells Reset 'This will remove screen refresh! Excel.Application.ScreenUpdating = False 'This will start the timer and save it in cell C1. starttime = Timer Sheets("Data").Range("C1") = starttime Sheets("Data").Range("C3") = "=R[-1]C-R[-2]C" 'Load file Load_file Excel.Application.ScreenUpdating = False 'This is to mark the whole data Mark_data 'This will set the number of cycle Nr_of_cycles 'This extract data from SQL Get_Data_from_SQL 'This creates the summary table Create_summary_table 'This will give the lenght of the jumbo Set_lenght 'this will give the slitters slitters 'This will give the winding of the jumbo winding 'This will give the set numset 'This will give the set numberset 'This will give the set numsetrep 'This will give roll lenght extract_MD 'This will give defects info on each roll roll 'This will erase the trim Deleterows 'This creates the graph Graph_inputs Graph 'This will end the timer endtime = Timer Sheets("Data").Range("C2") = endtime t1 = Sheets("Data").Range("C3") Sheets("Data").Select Range("B124", "B65536").Select Selection.ClearContents Sheets("Front").Select Range("D9").Select 'This will start screen refresh Excel.Application.ScreenUpdating = True 'This is the mmessage box to report calculation time. MsgBox "Time of analysis: " & Chr(9) & t1 & " sec" End Sub "Mike" wrote: Opps try this Sub test() If Range("A1") = "" Then MsgBox "Worksheet is empty" Exit sub Else MsgBox "Put Your Code Here" End If End Sub "answer33" wrote: Hi, I have macro to extract data and then I use the information to built graph and other things. The problem is when the data2 worksheet is empty (like if that day I didn' run the machine) the program goes all the way to the end but when I try to run it again I get an error message. I would like to add a if statement (or somehting else) to look in the worksheet data2 before it proceed with the rest of the programming and if it's empty go to the end of the program and give me an empty worksheet message. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Skip function if data sheet is empty
It may sound basic, but how can I define my label in that example?
I try a couple of time, but I always have the same error that my label is not define. "JW" wrote: Just place a label after the Graph sub program and use a GoTo statement to go there if data2 is empty. Dim ws As Worksheet Set ws = Worksheets("data2") If WorksheetFunction.CountA(ws.Cells) = 0 Then GoTo labalNameHere answer33 wrote: I like the suggestion but I also need that program to run in loop so if it's empty I'll recorded it empty and go to next file. Here the code i have. I need to check after Get_Data_from_SQL. If the Data2 worksheet is empty then go after the Graph sub program and complete the task. Thanks, S/bastien Sub Program() Dim t1 As Single 'Reset worksheets, clean cells Reset 'This will remove screen refresh! Excel.Application.ScreenUpdating = False 'This will start the timer and save it in cell C1. starttime = Timer Sheets("Data").Range("C1") = starttime Sheets("Data").Range("C3") = "=R[-1]C-R[-2]C" 'Load file Load_file Excel.Application.ScreenUpdating = False 'This is to mark the whole data Mark_data 'This will set the number of cycle Nr_of_cycles 'This extract data from SQL Get_Data_from_SQL 'This creates the summary table Create_summary_table 'This will give the lenght of the jumbo Set_lenght 'this will give the slitters slitters 'This will give the winding of the jumbo winding 'This will give the set numset 'This will give the set numberset 'This will give the set numsetrep 'This will give roll lenght extract_MD 'This will give defects info on each roll roll 'This will erase the trim Deleterows 'This creates the graph Graph_inputs Graph 'This will end the timer endtime = Timer Sheets("Data").Range("C2") = endtime t1 = Sheets("Data").Range("C3") Sheets("Data").Select Range("B124", "B65536").Select Selection.ClearContents Sheets("Front").Select Range("D9").Select 'This will start screen refresh Excel.Application.ScreenUpdating = True 'This is the mmessage box to report calculation time. MsgBox "Time of analysis: " & Chr(9) & t1 & " sec" End Sub "Mike" wrote: Opps try this Sub test() If Range("A1") = "" Then MsgBox "Worksheet is empty" Exit sub Else MsgBox "Put Your Code Here" End If End Sub "answer33" wrote: Hi, I have macro to extract data and then I use the information to built graph and other things. The problem is when the data2 worksheet is empty (like if that day I didn' run the machine) the program goes all the way to the end but when I try to run it again I get an error message. I would like to add a if statement (or somehting else) to look in the worksheet data2 before it proceed with the rest of the programming and if it's empty go to the end of the program and give me an empty worksheet message. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want the autofill/drag function to skip empty cells | Excel Discussion (Misc queries) | |||
paste data in different sheet but skip rows. | Excel Discussion (Misc queries) | |||
skip empty sheets | Excel Programming | |||
SKIP EMPTY ROWS FROM IMPORT | Excel Programming | |||
Skip empty and text cells | Excel Programming |