I am not sure now if it is faster (definitely not if the number of items is
small),
but an elegant way to do this is with SQL.

This is how that would go:
Dump the array in a sheet and give the range a name, say colours.
Have a field name in the very first row, say colour.
So for example your colours will be in A2 to A9 and in A1 you have the text
colour.
Then the range A1:A9 will be named colours.

Then in a normal module you have the following code:

Sub RunSheetSQL()

Dim rs As ADODB.Recordset
Dim objCommand As ADODB.Command
Dim strSheetConn As String
Dim objField As ADODB.Field
Dim strQuery As String
Dim strWBPath As String
Dim i As Long

On Error GoTo ERROROUT

strWBPath = ActiveWorkbook.FullName

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs "C:\TempSave.xls"

If InStr(1, strWBPath, ":\", vbBinaryCompare) 0 Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs strWBPath
Else
ActiveWorkbook.SaveAs "C:\TempSave2.xls"
End If

Application.DisplayAlerts = True

strSheetConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\TempSave.xls;" & _
"Extended Properties=Excel 8.0;"

Set rs = New ADODB.Recordset

strQuery = "select " & _
"colour as colour, " & _
"count(colour) as colour_count " & _
"from " & _
"colours " & _
"group by colour " & _
"order by 2 desc"

rs.Open Source:=strQuery, _
ActiveConnection:=strSheetConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText

If Not rs.EOF Then
Cells(2, 5).CopyFromRecordset rs
i = 5
For Each objField In rs.Fields
Cells(i) = objField.Name
i = i + 1
Next
End If

If Not rs Is Nothing Then
If rs.State = adStateOpen Then
rs.Close
End If
Set rs = Nothing
End If

Exit Sub
ERROROUT:

End Sub


You will need a reference to the Microsoft ActiveX Data Objects library.
This is under Tools, References in the Visual Basic editor.

Then just run the above code.

It may look complicated, but once you understand it you can adapt it and use
it for all kind of sheet range manipulations.
This SQL query is a select query, but you can do an update type of query as
well, so then you can alter the data
in the table you are running it on.
You could run the same code without saving and closing the workbook first,
but there is a memory leak bug in this and
you will see that the amount of memory taken up by Excel in the Windows task
manager will go up and up with every run
of this code. So you have to run it on a closed workbook, not an open
workbook.
If for some reason you don't want to run it on a worksheet range you could
run it on a text file, but that would involve more
code. Another option would be to use Access, but that would involve even
more code.


RBS




"quartz" wrote in message
...
I am using Office 2003 on Windows XP.

I need to be able to count the number of each item in a single element
array, then write out the counts.

For example, if the array contains:
Red, Blue, Red, Orange, Orange, Red, Green, Blue

My result would be:
A1: Red B1: 3
A2: Blue B2: 2
A3: Orange B3: 2
A4: Green B4: 1

If anyone has a function to which I could pass my array to output the
counts
or could share some code I could adapt it would be most appreciated.

Thanks much in advance.