Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Counting How Many Rows Have A Certain Criteria in Each Row's String

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.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>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"))
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading