VBA to find rows with specific text and count the zeros
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

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

  1. #11
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    3 Thread(s)

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

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

  2. #12
    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
    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.
    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 IntegerDim iTotal as Integer
    iTotal = 0
    For i = rngRow1 To rngRows
    If cells (i, 4) = "Sub-Total"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.

  3. #13
    Board Regular
    Join Date
    Feb 2009
    Posts
    550
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    Another approach:
    Code:
    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
    Osvaldo

    And God said to Noah: make a backup, cause I gonna format !

  4. #14
    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 Akuini View Post
    Try this:
    Code:
    Sub a1107502a()
    'https://www.mrexcel.com/forum/excel-questions/1107502-vba-find-rows-specific-text-count-zeros.html
    Dim c As Range, x As Long
    With Range("D:D")
        
        Set c = .Find(What:="Sub-Total", LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If Not c Is Nothing Then
            FirstAddress = c.Address
            Do
                x = x + WorksheetFunction.CountIf(Range("H" & c.Row & ":BE" & c.Row), 0)
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
        End If
    End With
    Range("C2") = x
    End Sub
    
    
    Amazing! This macro works perfectly! Thank you so much!

  5. #15
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    3 Thread(s)

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

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

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
  •