VBA to find rows with specific text and count the zeros

[rbone]

I'm looking for a macro to find multiple "Sub-Total" in Column D and count the number of "0"s in the corresponding rows.

There are many "0"s in the report, but I only want the "0"s in the Sub-Total rows.

Any assistance is greatly appreciated

 

[=ODIN=]

Does it need to be a macro?

You could use an array formula to do this in a helper column.


If your data has sub-total in column d, and you wanted to counted the zeros in columns e-h you could use this formula (assumes you enter it onto row 1 and then drag it down as far as needed).

=IF(D1="sub-total",SUM(--(E1:H1=0)),"Not A Sub Total Row")

However, it must be entered as an array formula. To do that, you would click into cell D1, paste the formula in, and hit Ctrl+Shift+Enter. You will know it worked correctly if you single click back on cell D1 and see the formula looking like this (notice curly brackets on ends)

{=IF(D1="sub-total",SUM(--(E1:H1=0)),"Not A Sub Total Row")}

It's worth noting that this method counts any blank cells as zero.

 

[rbone]

Yes, it does need to be in a macro. It's part of a much larger macro to pull numbers for reports. The code below is just a single, small macro for the entire project.

The last step of this macro would be to count the zeros in the rows that are Sub-Total Rows.

At the end of this macro, I would need it to look at sheet "Hour", count the zeros in the Sub-Total Rows and put the count in C2 on sheet "Hour (2)"

Sub SFDHourCopyFormating28()

    Sheets("Hour").Select
    Sheets("Hour").Copy After:=Sheets(12)
    Sheets("Hour (2)").Select
    Cells.Select
    If ActiveSheet.AutoFilterMode Then
        ActiveSheet.AutoFilterMode = False
    End If
    With Selection
        .VerticalAlignment = xlTop
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Selection.RowHeight = 12
    Range("D35").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("D36").Select
    Selection.Cut
    Range("D37").Select
    ActiveSheet.Paste
    Cells.Select
    ActiveWorkbook.Worksheets("Hour (2)").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Hour (2)").Sort.SortFields.Add2 Key:=Range( _
        "D1:D9997"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Hour (2)").Sort
        .SetRange Range("A1:IV9997")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(R86C8:R113C58,"">90"")"
    Range("C2").Select
'   Here is where I need the new code to count the zeros
End Sub

Here's an example of the data seen, and the reason to count the zeros in the Sub-total Rows only.

The image isn't showing for me, in case it isn't for you, here's the link. https://drive.google.com/open?id=1oPBPiOKcZVHvVPpA-SckYWE2xAENuOD8[TABLE="width: 518"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD="colspan: 4"][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD="colspan: 3"][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD="colspan: 3"][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD="colspan: 4"][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD="colspan: 3"][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD="colspan: 3"][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD="colspan: 3"][/TD]
[TD="colspan: 4"][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD="colspan: 3"][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD="colspan: 3"][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 3"][/TD]
[/TR]
</tbody>[/TABLE]

 

[SupremeDr]

Range("C2").Value = "=COUNTIF(addrangehre,0)"

 

[rbone]

Range("C2").Value = "=COUNTIF(addrangehre,0)"

Thank you, but that will count all zeros in a specific range. I can only use the zeros in the sub-total rows. There is 1 sub-total row for each date, and four other rows with many zeros for each date that I can not use. I'm hoping to avoid a separate =COUNTIF formula for each date and have the macro search for the sub-total rows and count the zeros.

 

[SupremeDr]

Thank you, but that will count all zeros in a specific range. I can only use the zeros in the sub-total rows. There is 1 sub-total row for each date, and four other rows with many zeros for each date that I can not use. I'm hoping to avoid a separate =COUNTIF formula for each date and have the macro search for the sub-total rows and count the zeros.

Sounds like you need to run a loop through each line with an if statement.

Dim wsHour2 As Excel.Worksheet
Set wsHour2 = Sheets("Hour 2")
Dim rngTable1 As Excel.Range
Set rngTable1 = wsHour2.Range("A1").CurrentRegion 'change A1 to the first cell in the table
rngRows = rngTable1.Rows.Count
rngRow1 = rngTable1.Rows(1).Count
Dim i As Long
[COLOR=#0000FF]Dim[/COLOR] iVal [COLOR=#0000FF]As Integer
Dim iTotal as Integer
iTotal = 0

For i = rngRow1 To rngRows
If cells (i, 4) = "Sub-Total"
[/COLOR]
iVal = Application.WorksheetFunction.COUNTIF(Range([COLOR=#CC0000]"A1:A10"[/COLOR]),[COLOR=#CC0000]"0"[/COLOR])
iTotal = iTotal + iVal
end if
next

Range("C2").Value = iTotal

end sub

 

[rbone]

Sounds like you need to run a loop through each line with an if statement.

Dim wsHour2 As Excel.Worksheet
Set wsHour2 = Sheets("Hour 2")
Dim rngTable1 As Excel.Range
Set rngTable1 = wsHour2.Range("A1").CurrentRegion 'change A1 to the first cell in the table
rngRows = rngTable1.Rows.Count
rngRow1 = rngTable1.Rows(1).Count
Dim i As Long
[COLOR=#0000FF]Dim[/COLOR] iVal [COLOR=#0000FF]As Integer
Dim iTotal as Integer
iTotal = 0

For i = rngRow1 To rngRows
If cells (i, 4) = "Sub-Total"
[/COLOR]
iVal = Application.WorksheetFunction.COUNTIF(Range([COLOR=#CC0000]"A1:A10"[/COLOR]),[COLOR=#CC0000]"0"[/COLOR])
iTotal = iTotal + iVal
end if
next

Range("C2").Value = iTotal

end sub

Thanks for this!!! I'm getting a syntax error on

If cells (i, 4) = "Sub-Total"

 

[Joe4]

Thanks for this!!! I'm getting a syntax error on

Code:
If cells (i, 4) = "Sub-Total"

There are a few errors with that line of code. There should not be a space after "cells", and it is missing a "Then".
Try:

If Cells(i, 4) = "Sub-Total" Then

 

[rbone]

There are a few errors with that line of code. There should not be a space after "cells", and it is missing a "Then".
Try:

If Cells(i, 4) = "Sub-Total" Then

Thank you! Getting closer!
If I start it at D48, it finds 0, if I start it at D340 it finds 1624. There should only be 3 zeros in the rows that contain "Sub-Total" in Column D in the entire report. Row 340 has the first 0 in a row that has "Sub-Total" in Column D, but the first Sub-Total row is Row 48.

I hope I'm making sense.

 

[rbone]

I hope this clarifies what I'm looking for. I'm hitting a huge brick wall.
I run monthly reports, so each month the number of days is different so I can't hard code a range. I also have 13 locations and each template is slightly different so I need the code to be somewhat dynamic.

If column D is "Sub-Total"
Then Select Row (Row range is H to BE)
Count number of zeros in row
Loop until complete
Output total count to Cell C2

It seems it would be more simple to just have a macro copy the found rows to a new sheet and do a =COUNTIF for the zeros? I'm trying to avoid this.