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


"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:OuRo7k7CGHA.528@TK2MSFTNGP09.phx.gbl...
> 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?
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "SJC" <stuart_crawley@blueyonder.co.uk> wrote in message
> news:e2jnM86CGHA.2292@tk2msftngp13.phx.gbl...
>> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
>> news:OIxCH7zCGHA.208@tk2msftngp13.phx.gbl...
>> > How about simply:
>> >
>> > For i = 0 to Ubound(Harray)
>> > If Harray(i) = "test" then
>> > n = n + 1
>> > End If
>> > Next
>> >
>> > Msgbox n
>> >
>> > RBS
>> >
>> > "SJC" <stuart_crawley@blueyonder.co.uk> wrote in message
>> > news:OLoHqRzCGHA.1288@TK2MSFTNGP09.phx.gbl...
>> >> 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?
>> >>
>> >
>>
>>
>
>