Looping to count instances across columns and rows

[nikhil1101]

Hi

I have three columns & 4000 rows with values in each cell that range from 0-60. I would like to could how many times each value occurs exclusively in each row for each column.

e.g

trying to count how many 18s for the 3 rows


row a 18 16 56
row b 60 18 18
roc c 55 18 02

The count for 18 in the scenario above would be 3. I'm assuming this will have to be looped in VB.

Thanks
Nik

 

[Scott T]

Try, this will generate a message box it can be change to put the result in a cell if that is what you want.

Sub countinstances()
Dim lr As Long
Dim mynum As Double
Dim mycount As Long
lr = Cells(Rows.count, 1).End(xlUp).Row
mynum = Val(InputBox("enter number to count"))
mycount = 0
For x = 1 To lr
    On Error Resume Next
    a = Application.Match(mynum, Range("A" & x & ":C" & x), 0)
    If IsError(a) = False Then
        mycount = mycount + 1
    End If
Next x
MsgBox ("the count is " & mycount)

End Sub