Khaleel,

Try this. It's worksheet code so right click the sheet tab and paste it in:-

Sub thehuntforjoe()
Dim mtRange As Range
Set myrange = Range("a1:c100") '<Change to suit
For Each c In myrange
If InStr(1, c.Value, "joe") 0 Then
Count = Count + 1
End If
Next c
MsgBox ("There are " & Count & " occuerences of joe in the range")
End Sub

Mike

"Khaleel" wrote:

i need to count the number of times a particular text string appears in a
sheet, and store that number in a variable.

the problem is....the text i want to search is a part of a larger text string.
eg:
say i want to count the number of times the word "joe" appears on a sheet...

the sheet contains data like
"joe is a good boy"
"i asked joe"
"compjoenis"

i should be able to write a command like :

N = {whatever solution you give}
[i should get a answer as 3]

so that i can use the variable "n" in my further calculations.

i hope i have conveyed my doubt....

any help on this is really appreciated.
thanks in advance.

Regards
Khaleel