Counting the number of times a string appears on a filtered worksheet.

[NWE]

Hi

So my goal is to count the number of times a string occurs in a filtered worksheet. The string is "Ex lamp did not match Proposed". I have the following code which keeps giving me a value of one.

Sub CountString()
Dim cl As Range
Dim rng1 As Range
Dim DD As Long
    Set rng1 = Range("B2:B200")


    
    For Each cl In rng1
    If cl.EntireRow.Hidden = False Then
        If cell.Value = "Ex lamp did not match Proposed." Then
                On Error Resume Next
                DD = Application.WorksheetFunction.CountIf(ActiveSheet.Cells, "Ex lamp did not match Proposed.")
                End If
                Worksheets("Chart&Calc").Range("K2") = DD


    End If
    Next cl
End Sub

Thoughts?

[Bob Phillips]

You define a variable cl, but use cell in one line, so should be

Sub CountString()
Dim cl As Range
Dim rng1 As Range
Dim DD As Long
    Set rng1 = Range("B2:B200")


    
    For Each cl In rng1
    If cl.EntireRow.Hidden = False Then
        If cl.Value = "Ex lamp did not match Proposed." Then
                On Error Resume Next
                DD = Application.WorksheetFunction.CountIf(ActiveSheet.Cells, "Ex lamp did not match Proposed.")
                End If
                Worksheets("Chart&Calc").Range("K2") = DD


    End If
    Next cl
End Sub

but really you just need to add them

Sub CountString()
Const FILTER_VALUE As String = "Ex lamp did not match Proposed."
Dim cl As Range
Dim rng1 As Range
Dim DD As Long

    Set rng1 = Range("B2:B200")

    For Each cl In rng1
    
        If cl.EntireRow.Hidden = False Then
        
            If cl.Value = FILTER_VALUE Then
                    
                DD = DD + 1
            End If
        End If
    Next cl
            
    Worksheets("Chart&Calc").Range("K2") = DD
End Sub

[Paul_Hossler]

You could probably save some looping

Option Explicit


Sub CountString()


    Const FILTER_VALUE As String = "Ex lamp did not match Proposed."


    Dim cl As Range
    Dim rng1 As Range
    Dim DD As Long


    With ActiveSheet
        Set rng1 = Intersect(.Rows("2:200"), .UsedRange).SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants, xlTextValues)


        For Each cl In rng1
            If cl.Value = FILTER_VALUE Then DD = DD + 1
        Next cl
            
        'Worksheets("Chart&Calc").Range("K2") = DD
        MsgBox DD
    
    End With


End Sub

I tried a CountIf() on rng1 insttead of the For Each but for some reason VBA didn't like it

[snb]

Sub M_snb()
    Cells(1).CurrentRegion.Columns(2).SpecialCells(12).Name = "snb"
    
    For Each it In [snb].Areas
       y = y + Application.CountIf(it, "aa1")
    Next
    
    MsgBox y
End Sub

[NWE]

Hey all,

so as I read through the responses I need to expand this a bit. So "Ex lamp did not match Proposed" would be visible in column G but the filtering would be based on options in Column B. So Column B would be filtered (Ex column B could say "Arcada") then column G would have several options, one of them being "Ex lamp did not match proposed". So the code would be looking at column G based off what is filtered in column B if that makes sense to count up each text string in column G.

[Paul_Hossler]

How about attaching a real life example workbook with realistic data, and the expected answer?

Meanwhile. maybe something this will work for your new requirements. It's not the fastest (no arrays) but is the most straight-forward (IMHO)

Option Explicit


Sub HowMany()
    Dim B As Range, R As Range
    Dim N As Long
    
    With ActiveSheet
        Set B = .Cells(2, 1).CurrentRegion.Columns(2).SpecialCells(xlCellTypeVisible)
        
        'MsgBox B.Address
    
        For Each R In B.Cells
            If R.Row > 1 Then   '   header
                If .Cells(R.Row, 7).Value = "Ex lamp did not match Proposed" Then N = N + 1
            End If
        Next


    End With
    
    MsgBox N


End Sub

[NWE]

Paul,

see the attached workbook. In Column B on Sheet1, that is what will be filtered, then countIF would be conducted on each one of the values in column G of Sheet 1 and that resultant would be inputed into "Chart&Calc" range K2. For my example I used "Ex lamp did not match Proposed." but all of those values will need the same approach. Hope that is clearer.

[Paul_Hossler]

I think a pivot table would be easier and more flexible

Capture.JPG

[NWE]

Thanks. I think the people above me got tunnel visioned on wanting to just "hit a button" but this should do as well. Also it seems any variant of the solutions above will work as well if I wanted to go the button route. Thanks for the help!

[Paul_Hossler]

Management always wants a 'push button' approach. Problems arise when they push the wrong button

Pivot tables with slicers can make a pseudo-dashboard fairly easily

Capture.JPG

[NWE]

Management always wants a 'push button' approach. Problems arise when they push the wrong button

Pivot tables with slicers can make a pseudo-dashboard fairly easily

Capture.JPG

No go on the pivot table but the button push with filtering makes them happen. They will have only one button to push. Thanks for the help sir!