What is wrong with this

[FatalExceptionError]

Public Function CountOccurance(Foo As Variant, Nums As Variant) As Variant

   '----------------------------------------------------------------------
    '** A function with one required arguments to count the occurences of a string
    '   and return the total occurences of that string in a range
    '   Can be called with formulas such as =CountString(F3), =CountString(Support).
   '----------------------------------------------------------------------
    Dim Count As Double, i As Variant
    On Error GoTo Handler
    Count = 0
    For Each Foo In Nums
        If Nums = Foo Then
            Count = Count + 1
    Next
    CountOccurance = Count
    Exit Function
Handler:
        Add3 = CVErr(2036)  'xlErrNum = 2036
End Function

when I make the file I get an error stating next without a for

I need that if statement so I can count the occurance of of something in a range of cells in excel. If cats always land on their feet
and Toast always lands buttered side up
What happens if you tape toast, buttered side up on a cat's back??????

 

[vb5prgrmr]

Either you need an end if...
[tt]
If Nums = Foo Then
Count = Count + 1
End If
[/tt]
or you need to put it on one line
[tt]
If Nums = Foo Then Count = Count + 1
[/tt]

Toast wins!

Good Luck

 

[rustylee]

Looks like you need and "End If" statement. By virtue of starting a new line with the "count = count + 1" line.

Try this

For Each Foo In Nums
If Nums = Foo Then
Count = Count + 1
End IF
Next
-- or --

For Each Foo In Nums
If Nums = Foo Then Count = Count + 1
Next

 

[JoshuaL]

I think that you're not going to accomplish what you're attempting with this code. It is fundamentally unsound.

You're trying to compare Nums, which, based on your For Each, must be a collection of Foos, to a Foo. You're trying to compare a whole collection of objects to a single object.
They'll never be equal.

If the machine this will be run on has Excel you may want to consider using Excel objects. For example...

(add a reference Microsoft Excel Obj library)
Public Function CountOccurence(value As string, myRange As Excel.Range) As string

'----------------------------------------------------------------------
'** A function with one required arguments to count the occurences of a string
' and return the total occurences of that string in a range
' Can be called with formulas such as =CountString(F3), =CountString(Support).
'----------------------------------------------------------------------
Dim Count As integer, i As integer, j as integer

On Error GoTo Handler
Count = 0
For i = 1 to myRange.rows.count
For j = 1 to myRange.columns.count
If cstr(MyRange(i, j).value) = value Then
Count = Count + 1
end if
Next
CountOccurence = Count
Exit Function
Handler:
Add3 = CVErr(2036) 'xlErrNum = 2036
End Function


Also, unless you're using vbscript somewhere in here you should reduce your use of variant. The comments mention that you're supposed to be using searching for a string.

HTH
josh

 

[ALEKSJAY]

I don't think that function does what you are trying to accomplish. Check if this helps:

Public Function CountOccurance(Foo As String, Nums As String) As Double
Dim count As Double, x As Integer
Dim L As Integer
L = 1
count = 0
Do While L <= Len(Nums)
x = InStr(L, Nums, Foo)
If x = 0 Then
Exit Do
ElseIf x > 0 Then
count = count + 1
L = x + 1
End If
Loop
CountOccurance = count
End Function