CountIf in VBA not counting criteria generated from formulas

Peteor

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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
2,061
Office Version
  1. 365
Platform
  1. Windows
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
150
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
73,212
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
24,208

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
2,061
Office Version
  1. 365
Platform
  1. Windows
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
150
Thank you everyone for your replies! Jeffrey Mahoney's last post solved my problem, and worked exactly as intended.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,016
Messages
5,834,947
Members
430,329
Latest member
asmith75

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
Top