skip actions within a macro if a cell value is zero

B

BeSmart

Hi All
(app is excel 2003)
I have a worksheet showing 9 weeks of data in rows (19:149) and columns (A:K).

I'm filtering and splits this data into weekly reports on their own
worksheets.

I've addressed each week of data separately within the macro so formulas
could change to lookup different data. Here's what the macro is currently
doing:

- copy the base worksheet (sheet 1)
- Advance filter for a specific week nominated via a Vlookup formula
- delete unwanted rows through deleting hidden rows
- update formulas to show information relevant to this week only (sourced
from totals data calculated on another worksheet)
- format the sheet (delete unnecessary buttons etc).

I'd like to add a check before running each section of the macro..

i.e. before copying the base worksheet (sheet 1), check that the total for
this new week is greater than zero (total value is in sheet2.cell("C101")).
otherwise
don't run this part of the macro (don't create the worksheet for this week)
- skip to the next section of the macro which will create a worksheet for
week 2 (after again checking that weeks total in Sheet2.cell("D101") is
greater than zero)
and so on through the macro..

Not sure if this is possible???

--
Thank for your help
BeSmart

example of code (sorry I'm a novice and I know my code is clumsy and not the
most effecient - but I'm self-taught and learning as I go...)
_____________________
Sub Split_Weeks()
Dim cell As Range
Dim myCell As Range
Set r = ActiveSheet.Range("A19:A148")
nLastRow = r.Rows.Count + r.Row - 1

.....
'''' code to create Week 1 starts here ''''
Sheets("Sheet 1").Select
Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "MBA" & " " & Sheets("Sheet2").Range("A8").Value
Range("B18").Select
ActiveCell.FormulaR1C1 = "=""=""&Sheet2!R[-10]C[-1]"
ActiveSheet.Range("A19:K148").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("B17:B18"), Unique:=False
For n = nLastRow To 1 Step -1
If Cells(n, "A").EntireRow.Hidden = True Then
Rows(n).Delete
End If
Next
ActiveSheet.ShowAllData
Range("E18").ClearContents ''(this section is formatting)''
ActiveSheet.Shapes("planned").Select
Selection.Delete
ActiveSheet.Shapes("week_list").Select
Selection.Delete
Range("G11").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP("" ""&MID(R[7]C[-5],3,10),WEEKS,2,FALSE)"
Range("G12").Select
'''' code to create Week 8 starts here '''''
Sheets("Sheet 1").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "MBA" & " " & Sheets("Sheet2").Range("A9").Value
Range("B18").Select
ActiveCell.FormulaR1C1 = "=""=""&Sheet2!R[-9]C[-1]"
Range("B19").Select
ActiveSheet.Range("A19:K148").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("B17:B18"), Unique:=False
For n = nLastRow To 1 Step -1
If Cells(n, "A").EntireRow.Hidden = True Then
Rows(n).Delete
End If
Next
ActiveSheet.ShowAllData
Range("E18").ClearContents
ActiveSheet.Shapes("planned").Select
Selection.Delete
ActiveSheet.Shapes("week_list").Select
Selection.Delete
Range("G11").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP("" ""&MID(R[7]C[-5],3,10),WEEKS,2,FALSE)"
Range("G12").Select
.....
 
N

Nigel

In general you can loop through a range (in your case C101, D101 etc)
checking a value to decide to run a subroutine. Example shown

Dim rngCheck as Range, rngC as Range
Set rngCheck = Sheet2.Range("C101:K101)
For each rngC in rngCheck
If rngC.Value > 0 then
Call YourSubroutine to run here
Else
'do something else if required
End If
Next

You might find that if you partition your code into sub routines it will be
a lot easier to write and modify.

Also within you code there is no need to select data and then act on it.
e.g.

ActiveSheet.Shapes("planned").Select
Selection.Delete

If far more efficient to use: ActiveSheet.Shapes("planned").Delete

Since you might not want or need to select a sheet or cell etc. before you
act on it use:
Sheets("Sheet1").Shapes("planned").Delete


--

Regards,
Nigel
(e-mail address removed)



BeSmart said:
Hi All
(app is excel 2003)
I have a worksheet showing 9 weeks of data in rows (19:149) and columns
(A:K).

I'm filtering and splits this data into weekly reports on their own
worksheets.

I've addressed each week of data separately within the macro so formulas
could change to lookup different data. Here's what the macro is currently
doing:

- copy the base worksheet (sheet 1)
- Advance filter for a specific week nominated via a Vlookup formula
- delete unwanted rows through deleting hidden rows
- update formulas to show information relevant to this week only (sourced
from totals data calculated on another worksheet)
- format the sheet (delete unnecessary buttons etc).

I'd like to add a check before running each section of the macro..

i.e. before copying the base worksheet (sheet 1), check that the total for
this new week is greater than zero (total value is in
sheet2.cell("C101")).
otherwise
don't run this part of the macro (don't create the worksheet for this
week)
- skip to the next section of the macro which will create a worksheet for
week 2 (after again checking that weeks total in Sheet2.cell("D101") is
greater than zero)
and so on through the macro..

Not sure if this is possible???

--
Thank for your help
BeSmart

example of code (sorry I'm a novice and I know my code is clumsy and not
the
most effecient - but I'm self-taught and learning as I go...)
_____________________
Sub Split_Weeks()
Dim cell As Range
Dim myCell As Range
Set r = ActiveSheet.Range("A19:A148")
nLastRow = r.Rows.Count + r.Row - 1

....
'''' code to create Week 1 starts here ''''
Sheets("Sheet 1").Select
Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "MBA" & " " & Sheets("Sheet2").Range("A8").Value
Range("B18").Select
ActiveCell.FormulaR1C1 = "=""=""&Sheet2!R[-10]C[-1]"
ActiveSheet.Range("A19:K148").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("B17:B18"), Unique:=False
For n = nLastRow To 1 Step -1
If Cells(n, "A").EntireRow.Hidden = True Then
Rows(n).Delete
End If
Next
ActiveSheet.ShowAllData
Range("E18").ClearContents ''(this section is formatting)''
ActiveSheet.Shapes("planned").Select
Selection.Delete
ActiveSheet.Shapes("week_list").Select
Selection.Delete
Range("G11").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(""
""&MID(R[7]C[-5],3,10),WEEKS,2,FALSE)"
Range("G12").Select
'''' code to create Week 8 starts here '''''
Sheets("Sheet 1").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "MBA" & " " & Sheets("Sheet2").Range("A9").Value
Range("B18").Select
ActiveCell.FormulaR1C1 = "=""=""&Sheet2!R[-9]C[-1]"
Range("B19").Select
ActiveSheet.Range("A19:K148").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("B17:B18"), Unique:=False
For n = nLastRow To 1 Step -1
If Cells(n, "A").EntireRow.Hidden = True Then
Rows(n).Delete
End If
Next
ActiveSheet.ShowAllData
Range("E18").ClearContents
ActiveSheet.Shapes("planned").Select
Selection.Delete
ActiveSheet.Shapes("week_list").Select
Selection.Delete
Range("G11").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(""
""&MID(R[7]C[-5],3,10),WEEKS,2,FALSE)"
Range("G12").Select
....
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro not returning correct data 4
Complie Syntax Error 6
Macro should Save and Overwrite file 1
Different Results from the Same Macro 2
Macro that deletes and pastes filterd rows and then some 2
Using Autofilter within a macro 3
File Name changes with dates and Deleting Rows of Zeros 0
relatetive reference-to perform a series of cal and return result 2

Top