Help with Simple Function

**Tomski** · 08-31-2005, 08:13 AM

Hi guys,

Am new to this forum but could do with some help. I've just started working in a bank and need to sharpen up on my excel. I'm not new to programming, but am to VB and its syntax. Also I've never been very good at debugging.

Anyway trying to write a simple function that takes a range then counts the number of time a string occurs in it. Heres my code:

Function CountStr(ran As Range, Str As String) As Integer

Dim temp As Integer
temp = 0
For Each x In ran
If x = Str Then temp = temp + 1
End If
Next x

CountStr = temp
End Function

Any ideas of why this is throwing an error, I'm sure its something simple. Any help would be nice.

Thanks

Tom

**dominicb** · 08-31-2005, 08:20 AM

Good afternoon Tomski

The problem is in your End If line. Take it out and your function will work fine.

You function could phrase your If statement like this:

If x = Str Then
temp = temp + 1
End If

or like this:

If x = Str Then temp = temp + 1

but not a combination of the two.

Or you could use the =COUNTIF() function to get the job done

HTH

DominicB