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" <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?
>

[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" <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?
>>
>

[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

"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?
>>>
>>
>
>

[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?


--
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?
> >>
> >
>
>

[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


"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?
>> >>
>> >
>>
>>
>
>