CountIf in VBA not counting criteria generated from formulas

Peteor

Board Regular
Joined
Mar 16, 2018
Messages
132
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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,768
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
 

Peteor

Board Regular
Joined
Mar 16, 2018
Messages
132
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
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 & "*")
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,779

ADVERTISEMENT

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
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,768
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
 

Peteor

Board Regular
Joined
Mar 16, 2018
Messages
132
Thank you everyone for your replies! Jeffrey Mahoney's last post solved my problem, and worked exactly as intended.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,960
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top