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(xlCellTypeConstant s, 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

27101

[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

27113

[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

27113

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!