CountIf in VBA not counting criteria generated from formulas

[Peteor]

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

 

[Jeffrey Mahoney]

Please try this:

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]

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]

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

Search_Variable = Application.WorksheetFunction.CountIf(Range_To_Search, "*" & myValue & "*")

 

[mikerickson]

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.)

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]

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.

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]

Thank you everyone for your replies! Jeffrey Mahoney's last post solved my problem, and worked exactly as intended.