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.

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.

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.

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.

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.

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.

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.

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.