Counting a Specific value within a column

I have added a count line to my code to count how many times a specific value pops up and then to display it. The size of the column can vary (A huge amount)I have learnt recently a good alternative (Faster than doing an "For Each" loop) is to use the Find function but I can't see any reading material on using "Find" to count the value.

This is just a rough draft of my count code.

 Sub test()

     Dim rng As Range
     Dim cell As Range

        Set rng = Sheet1.Range("H2:H200")

        Count = 0

        For Each cell In rng
            If cell.Value = "VALUE" Then
            Count = Count + 1
        End If 
    Next cell

    Sheet1.Range("O3").Value = Count

    Count = 0

End Sub

can someone point me in the right direction?

Thank you.

1 answer

  • answered 2018-10-15 15:42 Damian

    Try this:

    Sub Counting()
        Dim LastRow As Long, Count As Long, rng As Range, sheet1 As Worksheet
        Set sheet1 = ThisWorkbook.Sheets("whateverursheetiscalled")
        LastRow = sheet1.Range("H2").End(xlDown).Row
        Set Range = sheet1.Range("H2:H" & LastRow)
        Count = Application.CountIf(rng, "VALUE")
        sheet1.Range("O3") = Count
    End Sub