View Full Version : Counting the number of times a string appears on a filtered worksheet.
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
09-09-2020, 02:49 PM
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
09-09-2020, 03:55 PM
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
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
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
09-10-2020, 07:42 AM
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
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
09-10-2020, 09:17 AM
I think a pivot table would be easier and more flexible
27101
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
09-11-2020, 08:13 AM
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
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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.