VBA to find rows with specific text and count the zeros
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: VBA to find rows with specific text and count the zeros

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to find rows with specific text and count the zeros

    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

  2. #2
    Board Regular
    Join Date
    Dec 2009
    Posts
    277
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to find rows with specific text and count the zeros

    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).
    Code:
    =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)
    Code:
    {=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.
    Last edited by =ODIN=; Aug 20th, 2019 at 10:00 PM.
    My favorite Excel Add-in:= Nutilities

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to find rows with specific text and count the zeros

    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)"

    Code:
    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=1oP...ckYWE2xAENuOD8
    Last edited by rbone; Aug 21st, 2019 at 12:04 PM.

  4. #4
    New Member
    Join Date
    Aug 2019
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to find rows with specific text and count the zeros

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

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to find rows with specific text and count the zeros

    Quote Originally Posted by SupremeDr View Post
    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.

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to find rows with specific text and count the zeros

    Quote Originally Posted by rbone View Post
    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.

    [Code]
    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
    Dim iVal As Integer
    Dim iTotal as Integer
    iTotal = 0

    For i = rngRow1 To rngRows
    If cells (i, 4) = "Sub-Total"

    iVal = Application.WorksheetFunction.COUNTIF(Range("A1:A10"),"0")
    iTotal = iTotal + iVal
    end if
    next

    Range("C2").Value = iTotal

    end sub

  7. #7
    New Member
    Join Date
    Aug 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to find rows with specific text and count the zeros

    Quote Originally Posted by SupremeDr View Post
    Sounds like you need to run a loop through each line with an if statement.

    Code:
    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
    Dim iVal As Integer
    Dim iTotal as Integer
    iTotal = 0
    
    For i = rngRow1 To rngRows
    If cells (i, 4) = "Sub-Total"
    
    iVal = Application.WorksheetFunction.COUNTIF(Range("A1:A10"),"0")
    iTotal = iTotal + iVal
    end if
    next
    
    Range("C2").Value = iTotal
    
    end sub
    Thanks for this!!! I'm getting a syntax error on

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

  8. #8
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,383
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: VBA to find rows with specific text and count the zeros

    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:
    Code:
    If Cells(i, 4) = "Sub-Total" Then
    Last edited by Joe4; Aug 21st, 2019 at 02:23 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    New Member
    Join Date
    Aug 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to find rows with specific text and count the zeros

    Quote Originally Posted by Joe4 View Post
    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:
    Code:
    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.

  10. #10
    New Member
    Join Date
    Aug 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to find rows with specific text and count the zeros

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •