How about trying this:

Option Explicit
Sub Getcount()
Dim nodupes As Collection
Set nodupes = New Collection
Dim rng As Range
Dim cell As Range
Dim i As Long
With Worksheets("sheet1")
Set rng = .Range("d27", .Range("d27").End(xlDown))
End With
With Worksheets("sheet2")
.Range("C:D").ClearContents
On Error Resume Next
For Each cell In rng
nodupes.Add cell.Value, cell.Text
Next
For i = 1 To nodupes.Count
.Cells(i, "C").Value = nodupes.Item(i)
.Cells(i, "D").Formula _
= "=countif(" & rng.Address(external:=True) & ",C" & i & ")"
Next i
End With

End Sub




paragtim wrote:

I have been trying to work this out since the thread was raised. I'm
pretty sure the problem is between the keyboard and the seat.

The background:
Sheet1 has data in columns a - i. Columns A to c has time and date
info. Column d has the name of the agent raising the query.
Columns e - i has the outcomes from the task undertaken.
Sheet 1 has auto filters set on row 26 columns a - i
New data is added by inserting rows from row 29

What I want to do is generate a unique list of names on sheet 2 in
Column C with, along side each name, in Column D, the number of times
the agents name has appeared in Column D on sheet 1. For example Smith
6, Evans 3

Tom Olglvy!! sent me the code below but it generates an error

"Run Time Error 1004" Application defined or object-defined error

Can anyone help me put it right please

Many thanks

Sub Getcount()
Dim nodupes As Collection
Set nodupes = New Collection
Set rng = Range("sheet1!D27", Range("Sheet!d27").End(xlDown))
Range("sheet2!C:D").ClearContents
On Error Resume Next
For Each cell In rng
nodupes.Add cell.Value, cell.Text
Next
For i = 1 To nodupes.Count
Cells(i, "C").Value = nodupes.Item(i)
Cells(i, "D").Formula = "=countif(A:A,C" & i & ")"
Next i

End Sub

--
paragtim
------------------------------------------------------------------------
paragtim's Profile: http://www.excelforum.com/member.php...o&userid=34010
View this thread: http://www.excelforum.com/showthread...hreadid=537740

--

Dave Peterson