It just uses the countif formula with proper absolute and relative cell
referencing as if you entered it in the top cell and drag filled it down the
column.

--
Regards,
Tom Ogilvy


"John@NGC" wrote:

Thanks Tom, you make it look so easy! I wish I understood how it worked
without even a loop or an offset.
Appreciate your help.

"Tom Ogilvy" wrote:

Sorry, there was a typo:

Sub Countem()
Dim rng as Range
set rng = range(cells(1,"D"),cells(rows.count,"D").End(xlup) )
rng.offset(0,1).Formula = "=countif(A:A,D1)"
rng.offset(0,1).formula = rng.offset(0,1).value
End Sub


or you can just do
Sub countem1()
With Range("E1:E158")
.Formula = "=Countif(A:A,D1)"
.Formula = .Value
End With
end Sub

--
Regards,
Tom Ogilvy



"John@NGC" wrote:

Thanks Tom for the info, however I have to show my ignorance. When I set the
numbered list just as you said, I only get a count of 1 in "E1". (I am
running the macro from "E1" if that makes a difference.) My first number is
101 used 1 times. 103 however is used 3 times but I get nothing in "E3". My
unique list runs from "D1" thru "D158". By the way, I should have said 158
unique numbers in lieu of 152.
Please advise.

"Tom Ogilvy" wrote:

Assume the 152 unique number list is in column D and the original list is in
Column A

Sub Countem()
Dim rng as Range
set rng = range(cells(1,"D"),cells(1,"D").End(xlup))
rng.offset(0,1).Formula = "=countif(A:A,D1)"
rng.offset(0,1).formula = rng.offset(0,1).value
End Sub

--
Regards,
Tom Ogilvy


"John@NGC" wrote:

I have a list of numbers, most duplicates. I have a sorted list of the 152
unique numbers in a separate column. I need a VBA routine to list the times
each number is used beside the unique number column. Any help is appreciated.

John