VB Newbie - Counting occurences in array
 

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?

VB Newbie - Counting occurences in array
 

How about simply:

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

Msgbox n

RBS

"SJC" wrote in message
...
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?

VB Newbie - Counting occurences in array
 

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" wrote in message
...
How about simply:

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

Msgbox n

RBS

"SJC" wrote in message
...
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?

VB Newbie - Counting occurences in array
 

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" wrote in message
...
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" wrote in message
...
How about simply:

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

Msgbox n

RBS

"SJC" wrote in message
...
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?

VB Newbie - Counting occurences in array
 

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" wrote in message
...
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" wrote in message
...
How about simply:

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

Msgbox n

RBS

"SJC" wrote in message
...
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?

VB Newbie - Counting occurences in array
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
How about simply:

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

Msgbox n

RBS

"SJC" wrote in message
...
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?