Loop through all sheets problem 2

[MikeGeitner]

Hi,

I'm trying to count all the yellow cells from all the sheets in a workbook. The code I have so far adds the three test cells on the first sheet three times, but not any from the next two sheets. The msgbox returns "9".

Hmmmm, I know I'm close. Any hints?

Public Sub CountYellow()

Dim rowInd, colInd As Integer
Dim SheetCount As Integer
Dim YellowCount As Integer

Dim Current As Worksheet


For Each Current In Worksheets

        For rowInd = 1 To 15
            For colInd = 1 To 10
            
                With Cells(rowInd, colInd)
                   If .Interior.ColorIndex = 6 Then
                   SheetCount = SheetCount + 1
                   End If
                End With
               
YellowCount = SheetCount + YellowCount

            Next colInd
        Next rowInd
 
Next
MsgBox ("There are " & SheetCount & " yellow cells")

End Sub

 

[Zathras]

Try adding the line
[tt]
MsgBox ("There are " & YellowCount & " yellow cells")
[/tt]

 

[MikeGeitner]

That returns "1722" yellow cells.

 

[CBasicAsslember]

What is the purpose of the "YellowCount"? Modify the code:

For Each Current In Worksheets
      Current.Activate

 

[SkipVought]

Hi,

You need to reference the worksheet

For Each Current In Worksheets
    [b]with current[/b]
        For rowInd = 1 To 15
            For colInd = 1 To 10
            
                [b]With [red][u].[/u][/red]Cells(rowInd, colInd)[/b]
                   If .Interior.ColorIndex = 6 Then
                   SheetCount = SheetCount + 1
                   End If
                End With
               
YellowCount = SheetCount + YellowCount

            Next colInd
        Next rowInd
    [b]end with[/b]
Next

Skip,

_[red][/red]

 

[MikeGeitner]

CbasicAssembler,

Yes, there was no purpose for that indeed.

Added the Current.Activate......Perfect!

Thanks! Have a star!


Here it is again, in working order:

Public Sub CountYellow()

Dim rowInd, colInd As Integer
Dim SheetCount As Integer
Dim Current As Worksheet


For Each Current In Worksheets

Current.Activate

        For rowInd = 1 To 15
            For colInd = 1 To 10
            
                With Cells(rowInd, colInd)
                   If .Interior.ColorIndex = 6 Then
                   SheetCount = SheetCount + 1
                   End If
                End With
               
            Next colInd
        Next rowInd
 
Next

MsgBox ("There are " & SheetCount & " yellow cells")

End Sub

 

[MikeGeitner]

Skip,

Thank you too! That also works perfectly.

Mike

 

[SkipVought]

I woud advise against using Select and Activate for this kind of processing. It is unnecessary and in many cases, severely affects the performance of procedures.

Use the Sheet object instead, per my previously posted example.

Skip,

_[red][/red]

 

[IknowMe]

Public Sub CountYellow()
For Each Worksheet In Worksheets
TotalSheets = TotalSheets + 1
For ThisRow = 1 To 15
For ThisColumn = 1 To 10
If Worksheet.Cells(ThisRow, ThisColumn).Interior.ColorIndex = 6 Then
YellowCount = YellowCount + 1
End If
Next ThisColumn
Next ThisRow
Next
MsgBox "Sheets: " & TotalSheets & " Yellows: " & YellowCount
End Sub


Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[IknowMe]

That's what happens when I try and multi-task.

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[MikeGeitner]

Skip,

Good advice. I'll take it.

Thanks,

Mike