I am trying to count how many rows in a column have a certain criteria in their string. Example data is below:
96.001
96.001
96.002
97.0052
1201 - Mining
1201 - Drilling
1205 - Safety
This data occurs in column F. I want the code to check the column and count how many different cells contain a decimal in them & assign that count to a variable. So if the variable was X, in the above case X = 4.
Really been stuck on this and would appreciate any help.
>Solution :
As mentioned in the comments, load the data into the array and use Int:
Sub foo()
Dim arr() As Variant
arr = Range("F2:F8").Value
Dim i As Long
For i = LBound(arr, 1) To UBound(arr, 1)
If Int(arr(i, 1)) <> arr(i, 1) Then
Dim count As Long
count = count + 1
End If
Next
Debug.Print count ' returns 4
End Sub
EDIT:
Given your updated sample data, perhaps the following?
Sub foo()
Dim count As Long
count = Application.CountA(Range("F2:F8")) - Application.CountIfs(Range("F2:F8"), "*-*")
End Sub
This is equivalent to counting all populated cells that do not contain a -.
Even easier, as noted by @FunThomas, just use Count:
Count = Application.Count(Range("F2:F8"))