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