Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting occurences of a name | Excel Worksheet Functions | |||
Counting the occurences | Excel Worksheet Functions | |||
Counting Occurences of Hours of the Day | Excel Discussion (Misc queries) | |||
Counting Occurences | Excel Discussion (Misc queries) | |||
Help on counting occurences | Excel Programming |