Check out some similar questions!

rsdjimbie · Oct 11, 2009, 08:14 AM

Hi all,

I need a loop to zero values on sheets in my workbook. It will be best to have a look at the sheet attached, I will explain further.

The sheets vary in length from day to day.
For sheet "Emb" I need the columns "H" and "I" zeroed if grater than 0, starting at row 5 and is fixed at this row, but not the totals.
My problem is however the second "block", "machine 2" that does not always start from row 14 as per this example.

For the rest of the sheets, columns "O" and "T", starting at row 5 and is fixed, and again not zeroing the total.

Many thanks.

JBeaucaire · Oct 12, 2009, 04:08 AM

Try this macro:

Code:

Option Explicit
Option Compare Text

Sub ZeroValues()
Dim LR As Long, i As Long, ws As Worksheet
Application.ScreenUpdating = False

For Each ws In Worksheets
    Select Case ws.Name
        Case "EMB"
            LR = ws.Range("H" & Rows.Count).End(xlUp).Row
            For i = 5 To LR
                If ws.Cells(i, "A") <> "" Then
                    If ws.Cells(i, "H") > 0 Then ws.Cells(i, "H") = 0
                    If ws.Cells(i, "I") > 0 Then ws.Cells(i, "I") = 0
                End If
            Next i
        Case "Prod A" To "Prod D"
            For i = 5 To LR
                If ws.Cells(i, "A") <> "" Then
                    If ws.Cells(i, "O") > 0 Then ws.Cells(i, "O") = 0
                    If ws.Cells(i, "T") > 0 Then ws.Cells(i, "T") = 0
                End If
            Next i
    End Select
Next ws

Application.ScreenUpdating = True
End Sub

rsdjimbie · Oct 12, 2009, 09:27 AM

Is doing the work needed thank you.

But have a look at the newest attachment, the cells marked with blue in "Emb" sheet must not change, "H12:I13", my problem is these cells move up or down daily as rows are inserted or deleted, is this possibal?

Again, many thanks.

JBeaucaire · Oct 12, 2009, 10:43 AM

You need to remove the 45 empty module sheets in your workbook. Very confusing.

Then try this:

Code:

Option Explicit
Option Compare Text

Sub ZeroValues()
Dim LR As Long, i As Long, ws As Worksheet
Application.ScreenUpdating = False

For Each ws In Worksheets
    Select Case ws.Name
        Case "EMB"
            LR = ws.Range("H" & Rows.Count).End(xlUp).Row
            For i = 5 To LR
                Select Case ws.Cells(i, "A").Value
                    Case "", "A" To "Z"
                        'do nothing
                    Case Else
                        If ws.Cells(i, "H") > 0 Then ws.Cells(i, "H") = 0
                        If ws.Cells(i, "I") > 0 Then ws.Cells(i, "I") = 0
                End Select
            Next i
        Case "Prod A" To "Prod D"
            For i = 5 To LR
                If ws.Cells(i, "A") <> "" Then
                    If ws.Cells(i, "O") > 0 Then ws.Cells(i, "O") = 0
                    If ws.Cells(i, "T") > 0 Then ws.Cells(i, "T") = 0
                End If
            Next i
    End Select
Next ws

Application.ScreenUpdating = True
End Sub

rsdjimbie · Oct 12, 2009, 11:16 PM

The emty modules actually contains macros, had to get the file to a size that can be uploaded.

JBeaucaire · Oct 13, 2009, 05:30 AM

Ah, in that case, next time maybe you can save yourself a lot of erasing and try zipping the file.

Is this sufficiently resolved?

rsdjimbie · Oct 13, 2009, 09:30 AM

Zip, ah, forgetting the simple things when your mind is in a twist with VBA, next time.

Thank you very very much, the macro works beutifully and saves me plenty time, you rock JB.

rsdjimbie · Oct 13, 2009, 09:36 AM

Don't know if this is too much, but don't understand some of the VBA word used like "LR" and "Case". Could you walk me through the macro so I can build on my knowledge? If too much don't worry.

JBeaucaire · Oct 13, 2009, 11:32 AM

Anything on the DIM line is variables that I am defining. I use LR in all my macros to store the "last row" value. Notice a little further down I "solve" the LR with the LR = ws.Range....? That's me using my variable.

Select Case is a VBA equivalent of stacking IF statements in a worsheet formula. If you know you are going be evaluating some piece of data over and over, and when it equals one value then DO THIS, and if it equals this other value then DO THAT, the Select Case is the method to make that easy to read and manage.

First you set the SELECT CASE?? And the?? Is the value you're evaluating. In your macro, it's the worksheet name.

We use Case "emb" as the first "If the worksheet name is emb, then DO THIS". All the code inside the case is executed. The next CASE statement signifies the start of the next "if the worksheet name is ???????, then DO THIS"...

These cases allow you to code specific behaviors based on different values.

JBeaucaire · Oct 13, 2009, 01:29 PM

Here's a fully commented version of the code:

Code:

Sub ZeroValues()
'Declare all the variables I'm going to use
    Dim LR As Long
    Dim i As Long
    Dim ws As Worksheet
'Turn off screen refreshing so code executes faster
    Application.ScreenUpdating = False

'Cycle through all the worksheets one at a time
    For Each ws In Worksheets
    'Look at each sheet name and run code specific to each sheet
    'store the worksheet name
        Select Case ws.Name
        'if the sheet name is "EMB"
            Case "EMB"
            'Find the Last row of data by looking at column H
                LR = ws.Range("H" & Rows.Count).End(xlUp).Row
            'Loop through all the rows from row 5 through the last row
                For i = 5 To LR
                'another select case section
                'store the value from column A for this row
                    Select Case ws.Cells(i, "A").Value
                    'if cell "A" is blank or has text in it, do nothing
                        Case "", "A" To "Z"
                            'do nothing
                    'for the numeric cells, change all values to zero
                        Case Else
                            If ws.Cells(i, "H") > 0 Then ws.Cells(i, "H") = 0
                            If ws.Cells(i, "I") > 0 Then ws.Cells(i, "I") = 0
                    End Select
            'next row on this sheet
                Next i
        'If the sheet name is Prod A - D
            Case "Prod A" To "Prod D"
            'Find the Last row of data by looking at column H
                LR = ws.Range("H" & Rows.Count).End(xlUp).Row
            'Loop through all the rows from row 5 through the last row
                For i = 5 To LR
                'If there is a value in column A....
                    If ws.Cells(i, "A") <> "" Then
                    '...then change values in O and T to zero
                        If ws.Cells(i, "O") > 0 Then ws.Cells(i, "O") = 0
                        If ws.Cells(i, "T") > 0 Then ws.Cells(i, "T") = 0
                    End If
            'next row on this sheet
                Next i
    'No more "cases" for sheet names
        End Select
'Move on to the next sheet and start the loop above all over again
    Next ws

Application.ScreenUpdating = True
End Sub

Here's a slightly tweaked version, I noticed I'd forgotten the LR solution for the second case.

Code:

Option Explicit
Option Compare Text

Sub ZeroValues()
Dim LR As Long, i As Long, ws As Worksheet
Application.ScreenUpdating = False

    For Each ws In Worksheets
        Select Case ws.Name
            Case "EMB"
                LR = ws.Range("H" & Rows.Count).End(xlUp).Row
                For i = 5 To LR
                    Select Case ws.Cells(i, "A").Value
                        Case "", "A" To "Z"
                            'do nothing
                        Case Else
                            If ws.Cells(i, "H") > 0 Then ws.Cells(i, "H") = 0
                            If ws.Cells(i, "I") > 0 Then ws.Cells(i, "I") = 0
                    End Select
                Next i
            Case "Prod A" To "Prod D"
                LR = ws.Range("H" & Rows.Count).End(xlUp).Row
                For i = 5 To LR
                    If ws.Cells(i, "A") <> "" Then
                        If ws.Cells(i, "O") > 0 Then ws.Cells(i, "O") = 0
                        If ws.Cells(i, "T") > 0 Then ws.Cells(i, "T") = 0
                    End If
                Next i
        End Select
    Next ws

Application.ScreenUpdating = True
End Sub

rsdjimbie · Oct 15, 2009, 12:47 PM

Thank you very much! What software are you using to construct your macros? I just don't see myself getting all the spaces and functions in the right place by typinh the macro from scratch. Amazing what actually can be done with macros.
Awsome work JB.

JBeaucaire · Oct 15, 2009, 03:57 PM

I type them manually into the VBEditor. Is there a VBA wizard?

Indenting logic sections is done easily with TAB as I go and it makes the code easy to read as "sections".

I use Option Explicit at the top of all my macros (In fact my computer puts it in automatically when I create a new module, it's a setting somewhere in the VBEditor Tools > Options > Auto Syntax Check.)
Option Explicit is the equivalent of "Spell check" and if I type something Excel doesn't recognize, it tells me, immediately or when I run the Debug > Compile VBAProject command.

It's important to always work with Option Explicit if you declare a lot of your own variables.

rsdjimbie · Oct 18, 2009, 12:06 PM

Thank you very much, ia teaching me a so many new things, like the "case" statement.

rsdjimbie · Oct 25, 2009, 12:30 PM

Added two lines of code to delete rows giving a certain criteria, but it is deleting the worksheets copmletly blank:eek: What am I missing here?

Code:

Option Explicit
Option Compare Text

Sub ZeroValues()
Dim LR As Long, i As Long, ws As Worksheet
Application.ScreenUpdating = False

For Each ws In Worksheets
    Select Case ws.Name
        Case "EMB"
            LR = ws.Range("H" & Rows.Count).End(xlUp).Row
            For i = 5 To LR
                Select Case ws.Cells(i, "A").Value
                    Case "", "A" To "Z"
                        'do nothing
                    Case Else
                        If ws.Cells(i, "H") > 0 Then ws.Cells(i, "H") = 0
                        If ws.Cells(i, "I") > 0 Then ws.Cells(i, "I") = 0
                        If ws.Cells(i, "F") = ws.Cells(i, "G") Then ws.Rows.Delete
                End Select
            Next i
        Case "Prod A" To "Prod D"
            For i = 5 To LR
                If ws.Cells(i, "A") <> "" Then
                    If ws.Cells(i, "O") > 0 Then ws.Cells(i, "O") = 0
                    If ws.Cells(i, "T") > 0 Then ws.Cells(i, "T") = 0
                    If ws.Cells(i, "U") = 0 & ws.Cells(i, "W") = 0 Then ws.Rows.Delete
                End If
            Next i
    End Select
Next ws

Application.ScreenUpdating = True
End Sub

JBeaucaire · Oct 25, 2009, 03:55 PM

It's doing what you told it to do.

Code:

ws.Rows.Delete

... means "delete all rows on the worksheet".

Maybe you meant this:

Code:

ws.Rows(i).Delete

Also, if you're going to delete rows as you run through the data, that will cause all the rows below to shift upward. That makes the row you HAVEN'T evaluated yet move up into the row you're currently on, and then the loop moves down to the next row.

The net effect of this is that each time a row is deleted, the next row of data isn't evaluated, it's essentially "skipped" because of the way it moved on you.

So, if you're going to delete rows, always work from the BOTTOM of the data set upward. This way you've already evaluated all the data below that is moving upward with each deletion.

To reverse the order the data is evaluated change this:

Code:

For i = 5 To LR

...change that to

For i = LR to 5 Step -1

rsdjimbie · Oct 26, 2009, 11:48 AM

No joy. Deletes al sorts of info on sheets.

JBeaucaire · Oct 26, 2009, 01:50 PM

Feel free to post up a sheet you are running this code on with your version of the macro installed. If you include a DESIRED RESULTS page, too, I can compare what is going on to what you're shooting for.

rsdjimbie · Oct 27, 2009, 11:24 AM

Here are the sheets.

rsdjimbie · Oct 28, 2009, 02:19 PM

JB, got it sorted, thank you very much for help on this one.