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

 

[Akuini]

Try this:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1107502a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1107502-vba-find-rows-specific-text-count-zeros.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] c [COLOR=Royalblue]As[/COLOR] Range, x [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]With[/COLOR] Range([COLOR=brown]"D:D"[/COLOR])
    
    [COLOR=Royalblue]Set[/COLOR] c = .Find(What:=[COLOR=brown]"Sub-Total"[/COLOR], LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=[COLOR=Royalblue]False[/COLOR], SearchFormat:=[COLOR=Royalblue]False[/COLOR])
    [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] c [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        FirstAddress = c.Address
        [COLOR=Royalblue]Do[/COLOR]
            x = x + WorksheetFunction.CountIf(Range([COLOR=brown]"H"[/COLOR] & c.Row & [COLOR=brown]":BE"[/COLOR] & c.Row), [COLOR=crimson]0[/COLOR])
            [COLOR=Royalblue]Set[/COLOR] c = .FindNext(c)
        [COLOR=Royalblue]Loop[/COLOR] [COLOR=Royalblue]While[/COLOR] [COLOR=Royalblue]Not[/COLOR] c [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]And[/COLOR] c.Address <> FirstAddress
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
Range([COLOR=brown]"C2"[/COLOR]) = x
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]

[/FONT]

 

[SupremeDr]

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.

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 IntegerDim iTotal as Integer
iTotal = 0
For i = rngRow1 To rngRows
If cells (i, 4) = "Sub-Total"[/COLOR]then
iVal = Application.WorksheetFunction.COUNTIF(Range("H" & i & ":BE" & i),0)
iTotal = iTotal + iVal
end if
next

Range("C2").Value = iTotal

end sub

This might work too.

 

[Osvaldo Palmeiro]

Another approach:

Sub CountZeros()
 Dim LR As Long
  LR = Cells(Rows.Count, 4).End(3).Row
  [C2] = Evaluate("=SUMPRODUCT(--(D2:D" & LR & "=""Sub-Total"")*(H2:BE" & LR & "=0))")
End Sub

 

[rbone]

Try this:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1107502a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1107502-vba-find-rows-specific-text-count-zeros.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] c [COLOR=Royalblue]As[/COLOR] Range, x [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]With[/COLOR] Range([COLOR=brown]"D:D"[/COLOR])
    
    [COLOR=Royalblue]Set[/COLOR] c = .Find(What:=[COLOR=brown]"Sub-Total"[/COLOR], LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=[COLOR=Royalblue]False[/COLOR], SearchFormat:=[COLOR=Royalblue]False[/COLOR])
    [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] c [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        FirstAddress = c.Address
        [COLOR=Royalblue]Do[/COLOR]
            x = x + WorksheetFunction.CountIf(Range([COLOR=brown]"H"[/COLOR] & c.Row & [COLOR=brown]":BE"[/COLOR] & c.Row), [COLOR=crimson]0[/COLOR])
            [COLOR=Royalblue]Set[/COLOR] c = .FindNext(c)
        [COLOR=Royalblue]Loop[/COLOR] [COLOR=Royalblue]While[/COLOR] [COLOR=Royalblue]Not[/COLOR] c [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]And[/COLOR] c.Address <> FirstAddress
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
Range([COLOR=brown]"C2"[/COLOR]) = x
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]

[/FONT]

Amazing! This macro works perfectly! Thank you so much!

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.