For Each loop error VBA

Question

I have written a code which do the next things:

clears defined ranges of cells and remove any background color
(except first tab);
resets the tab color;

I took a hint from here: Excel VBA For Each Worksheet Loop

Here it is:

Sub ClearAll()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Index <> 1 Then
            Call clear(ws)
        End If
    Next
End Sub
Sub clear(ws As Worksheet)
    With ActiveSheet
        .Tab.ColorIndex = xlColorIndexNone
        .Range(Cells(5, 7), Cells(.Cells(.Rows.Count, "N").End(xlUp).Row - 1, 14)).ClearContents
        .Range(Cells(5, 1), Cells(.Cells(.Rows.Count, "D").End(xlUp).Row - 1, 4)).ClearContents
        .Range(Cells(5, 7), Cells(.Cells(.Rows.Count, "N").End(xlUp).Row - 1, 14)).Interior.ColorIndex = 0
        .Range(Cells(5, 1), Cells(.Cells(.Rows.Count, "D").End(xlUp).Row - 1, 4)).Interior.ColorIndex = 0
    End With
End Sub

But after running it I got an error Run-time error '1004': Application-defined or object defined error.

I have altered the code. It looks terrible :(. But at least it works.

Sub ClearAll1()
    Dim quantWs As Integer
    Dim a As Integer

    quantWs = ActiveWorkbook.Worksheets.Count
    a = 2
    Do While a <= quantWs
        Worksheets(a).Activate
        ActiveSheet.Tab.ColorIndex = xlColorIndexNone
        Call clear
        a = a + 1
    Loop
End Sub
Sub clear(ws As Worksheet)
    With ActiveSheet
        .Tab.ColorIndex = xlColorIndexNone
        .Range(Cells(5, 7), Cells(.Cells(.Rows.Count, "N").End(xlUp).Row - 1, 14)).ClearContents
        .Range(Cells(5, 1), Cells(.Cells(.Rows.Count, "D").End(xlUp).Row - 1, 4)).ClearContents
        .Range(Cells(5, 7), Cells(.Cells(.Rows.Count, "N").End(xlUp).Row - 1, 14)).Interior.ColorIndex = 0
        .Range(Cells(5, 1), Cells(.Cells(.Rows.Count, "D").End(xlUp).Row - 1, 4)).Interior.ColorIndex = 0
    End With
End Sub

Could someone point what gone wrong in the first sample? Because the working version is so counterproductive...

Thank you for any help.

Moosli · Accepted Answer · 2017-09-05T08:47:07.333

You need to use the ws Object and not ActiveSheet in your Clear Sub

And i would Change ActiveWorkbook to ThisWorkbook beacuse if you have more then one workbook Open there are changes that excel will get the Wrong Workbook. If you Intrestet why this is so, you can read it her: VBA ACTIVEWORKBOOK VS THISWORKBOOK

Sub ClearAll()
        Dim ws As Worksheet
        For Each ws In ThisWorkbook.Worksheets
            If ws.Index <> 1 Then
                Call clear(ws)
            End If
        Next
    End Sub
    Sub clear(ws As Worksheet)
        With ws 
            .Tab.ColorIndex = xlColorIndexNone
            .Range(.Cells(5, 7), .Cells(.Cells(.Rows.Count, "N").End(xlUp).Row - 1, 14)).ClearContents
            .Range(.Cells(5, 1), .Cells(.Cells(.Rows.Count, "D").End(xlUp).Row - 1, 4)).ClearContents
            .Range(.Cells(5, 7), .Cells(.Cells(.Rows.Count, "N").End(xlUp).Row - 1, 14)).Interior.ColorIndex = 0
            .Range(.Cells(5, 1), .Cells(.Cells(.Rows.Count, "D").End(xlUp).Row - 1, 4)).Interior.ColorIndex = 0
        End With
    End Sub

The cells inside the ranges need to be qualified. `.Range(.Cells(5, 7), .Cells(.Cells(.Rows.Count, "N").End(xlUp).Row - 1, 14))` — , Sep 05 '17 at 08:39
Thanks a lot for the help, guys. Seems to be working just fine now. — BangBoy, Sep 05 '17 at 08:58

score 2 · Answer 2 · answered Sep 05 '17 at 08:57

You simply need to change With ActiveSheet to With ws but I think this is much easier to read:

Sub clear(ws As Worksheet)
    With ws
        .Tab.ColorIndex = xlColorIndexNone
        With .Range(.Cells(5, 7), .Cells(.Rows.Count, "N").End(xlUp).Offset(-1))
            .ClearContents
            .Interior.ColorIndex = 0
        End With

        With .Range("A5", .Range("D" & .Rows.Count).End(xlUp).Offset(-1)))
            .ClearContents
            .Interior.ColorIndex = 0
        End With

    End With
End Sub

For Each loop error VBA

2 Answers2