Conditional Macro (skip actions within a macro if a cell value is zero in a particular sheet).

[omairhe]

hello.!
please if any one can look into this kind of macro.....

skip actions within a macro if a cell value is zero


http://www.microsoft.com/communitie...fb-bfda-37f1b60d8f39&cat=&lang=&cr=&sloc=&p=1


Question

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.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.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
....
--
Thank for your help
BeSmart

 

[Jonmo1]

Welcome to the board...

Without actually looking into detail at your macro, the basic Idea is to use an IF..

IF Sheets("SheetName").Range("A1") = 0 Then
     'Do Nothing
Else
     'What to do when the cell is NOT 0
End If

 

[omairhe]

Hiiii thanks for the awesome reply.... works fine.. heres what i did...



Sub Macro2()
'
' Macro2 Macro
'

'
If Sheets("Sheet1").Range("E4") = 0 Then
'Do Nothing
Else
'
ActiveSheet.Range("$A$1:$V$8900").AutoFilter Field:=6, Criteria1:=">0", _
Operator:=xlAnd
Columns("F:F").Select
Selection.Copy
Sheets("Sheet3").Select
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste
End If
End Sub