VB Newbie - Counting occurences in array

[SJC]

I'm sure this is a really basic question but it has me stumped. I'm taking
the text strings in a user-defined range, converting them using a custom
function and putting the converted string into an array. I now need to count
the number of ocurrences of each string within the array to test for
duplicates. The conversion runs as follows:

i = 0
For Each cell In UserRange
Surname = cell.Value
Harray(i) = HOADEX(Surname)
i = i + 1
Next cell

Harray is Dim'd as string and HOADEX is the custom function. How do I count
the number of times a given string appears in the array?

 

[RB Smissaert]

How about simply:

For i = 0 to Ubound(Harray)
If Harray(i) = "test" then
n = n + 1
End If
Next

Msgbox n

RBS

 

[SJC]

Thanks for that, I had tried something similar but the Ubound makes more
sense than the way I had tried. However, I have another problem with nesting
this in another For..Next:

For Each Cell In UserRange
Surname = Cell.Value
For i = 0 To UBound(Harray)
If Harray(i) = HOADEX(Surname) Then
n = n + 1
End If
Next i
MsgBox n
Next Cell

The debugger indicates that the Next Cell is causing a problem. I don't
understand why. I have a very similar piece of code elsewhere that works
without any problems. (I realise the code above doesn't actually achieve
anything at the moment, once I've resolved this problem I will be creating a
routine to deal with n values >1) Any suggestions? TIA

SJC

 

[RB Smissaert]

Best show the whole (relevant) code, for example how did you declare Cell
and UserRange?
The other thing to mention is that I don't think you need the extra variable
Surname. Just do HOADEX on Cell.Text.

RBS

 

[Tom Ogilvy]

Sub RA()
Dim Cell As Range, UserRange As Range
Dim Surname As Variant, Harray As Variant
Set UserRange = ActiveSheet.UsedRange
Harray = Array("A1", "B2", "C3")
For Each Cell In UserRange
Surname = Cell.Value
For i = 0 To UBound(Harray)
If Harray(i) = HoadEx(Surname) Then
n = n + 1
End If
Next i
Next Cell
End Sub
Function HoadEx(aa)
HoadEx = aa
End Function

works well. So how is yours different?

 

[SJC]

It works now. I hadn't Dim'd Cell as range. I cleaned up some other bits at
the same time so I don't know for certain that was it but whatever it was
it's now working. Thanks ever so much for the help. Greatly appreciated.

SJC