CountIf in VBA not counting criteria generated from formulas

Peteor

Board Regular
Joined
Mar 16, 2018
Messages
152
Hey everyone! I would like my code (pasted below) to not only count all instances of variable "myValue", but to also count instances of myValue which is generated with formulas. As is, it will only count cells where myValue actually is the value rather than also counting cells where myValue is generated from a formula. Should I be using the cell.Value method with an If statement instead?

Sub String_Counter()


Dim myValue As Variant
Dim Range_To_Search As Range
Dim Search_Variable As Integer


MsgBox ("I will assume you would like to count something on the active sheet")


myValue = InputBox("What range of cells would you like to search? (input must be a continuous range)" & vbNewLine & vbNewLine & "Example:" & vbNewLine & "A1:C45 - This searches all of columns A, B, and cells 1 - 45 of column C" & vbNewLine & vbNewLine & "Example:" & vbNewLine & "A:A - This searches all of column A for a value")


Set Range_To_Search = Range(myValue)


myValue = InputBox("What string or number would you like to count the instances of within your specified range?" & vbNewLine & vbNewLine & "(Type an exact representation of what you wish to search for. Capitalization and spacing matters)")


Search_Variable = Application.WorksheetFunction.CountIf(Range_To_Search, myValue)


MsgBox (myValue & " appears " & Search_Variable & " times within the specified range")


End Sub
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Please try this:

Code:
Sub String_Counter()
  Dim myValue As Variant
  Dim Sht As Worksheet
  Dim Rng As Range
  Dim A As String
  Dim Cel As Range
  Dim vCount As Long
  Dim fCount As Long
  
  Set myValue = Application.InputBox("Please select a continuous range", "Select a Range", Type:=8)
  If myValue Is Nothing Then Exit Sub
  Set Rng = myValue
  
  myValue = InputBox("What string or number would you like to count the instances of within your specified range?" & vbNewLine & vbNewLine & "(Type an exact representation of what you wish to search for. Capitalization and spacing matters)")
  If myValue = "" Then Exit Sub
  
  For Each Cel In Rng
    A = Cel.Value
    If A = myValue Then
      If Left(Cel.Formula, 1) = "=" Then
        fCount = fCount + 1
      Else
        vCount = vCount + 1
      End If
    End If
  Next Cel
  
  MsgBox "There were " & vCount & " instances where that was found as a value" & vbNewLine & vbNewLine & _
          "There were " & fCount & " instances where that was found as a result of a formula"
  
  
End Sub
 
Upvote 0
I like how you set it up, and I like how it is now a selectable range. My concern however is using the "Cel.Value" method, only instances where myValue IS the value are returned. How does one address if A. myValue is not the only thing in the cell and B. If the myValue from A. is formula generated?
 
Upvote 0
Your code should count all cells regardless of whether they are hard values or formulae.
If you want to count cells that contain your value try
Code:
Search_Variable = Application.WorksheetFunction.CountIf(Range_To_Search, "*" & myValue & "*")
 
Upvote 0
The COUNTIF function counts the cells that have the indicated value.
If a cell contatins a formula the value (that CountIf) looks at is the result of that formula.
i.e. COUNTIF cannot distinguish between cells with user entered constants and cells with formulas.

To count the cells that do not have a formula and which contain (but might not equal) myValue a loop like this would be needed. (The LCase is to make things case insensitive.)
Code:
For each oneCell in Range_To_Search
    With oneCell
        If Not .HasFormula Then
            If LCase(.Value) Like "*" & LCase(myValue) & "*" Then
                Total = Total + 1
            End If
        End If
    End With
Next oneCell
 
Upvote 0
In my code above, it only returns a count increase if the "Value" of the cell is exactly as the user typed. It won't return a count increase if the text the user type is part of the cell contents.

If you want to search for a partial string and don't care about case, then use the code below. If you do wan to search for case sensitive data, then remove the UCASE() commands.

Code:
Sub String_Counter()
  Dim myValue As Variant
  Dim Sht As Worksheet
  Dim Rng As Range
  Dim A As String
  Dim Cel As Range
  Dim vCount As Long
  Dim fCount As Long
  
  Set myValue = Application.InputBox("Please select a continuous range", "Select a Range", Type:=8)
  If myValue Is Nothing Then Exit Sub
  Set Rng = myValue
  
  myValue = InputBox("What string or number would you like to count the instances of within your specified range?" & vbNewLine & vbNewLine & "(Type an exact representation of what you wish to search for. Capitalization and spacing matters)")
  If myValue = "" Then Exit Sub
  
  myValue = UCase(myValue)
  For Each Cel In Rng
    A = UCase(Cel.Value)
    If InStr(A, myValue) > 0 Then
      If Left(Cel.Formula, 1) = "=" Then
        fCount = fCount + 1
      Else
        vCount = vCount + 1
      End If
    End If
  Next Cel
  
  MsgBox "There were " & vCount & " instances where that was found as a value" & vbNewLine & vbNewLine & _
          "There were " & fCount & " instances where that was found as a result of a formula"
  
  
End Sub
 
Upvote 0
Thank you everyone for your replies! Jeffrey Mahoney's last post solved my problem, and worked exactly as intended.
 
Upvote 0

Forum statistics

Threads
1,218,539
Messages
6,143,085
Members
450,461
Latest member
Bosavon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top