CODEWITHSUNDEEP
excelvba
Justin Burns
Justin Burns

Reputation: 1

Count cells that are bold and have a specific value from specific range across specific worksheets

I have a workbook with multiple sheets.
I want to return the count of all cells in worksheets named Game1:Game50 in range Q7:U269 that are both bold and have the value of cell A4.

I can count all the values that meet the criteria in one sheet (see code below).
How do I apply the formula across each of the specified worksheets?
I don't want to loop through all sheets

Function CountBoldValue(Rng As Range, val As Range) As Long
    Dim Cell As Range
    For Each Cell In Rng
        If Cell.Font.Bold And Cell.Value = val Then
            CountBoldValue = CountBoldValue + 1
        End If
    Next Cell
End Function

Content in the cell:

=SUM(COUNTIF(INDIRECT("'"&A$35:A$38&"'!V$7:Z$269"),A6))

A35:A38 contain the names of the different worksheets I want to open (I only created a couple of them for now). I'm not sure whether to use the Indirect function to pass the range of sheets or to code the sheet names directly into the CountBoldValue function.

Upvotes: 0

Views: 51

Answers (1)

Tim Williams
Tim Williams

Reputation: 166196

You could do something like this:

Sub Tester()
    Debug.Print CountBoldValue([A1:D8], [J1])
End Sub


Function CountBoldValue(Rng As Range, val As Range) As Long
    Dim c As Range, ws As Worksheet, v
    v = val.Value 'read this once...
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name Like "Game#*" Then 'only count relevant sheets
            For Each c In ws.Range(Rng.Address).Cells
                If c.Value = v Then 'faster to split up these 2 tests
                    If c.Font.Bold Then
                        CountBoldValue = CountBoldValue + 1
                    End If
                End If
            Next c
        End If
    Next ws
End Function

Upvotes: 0

Related Questions