count no. of times a text appears in a sheet.
 

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

count no. of times a text appears in a sheet.
 

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

count no. of times a text appears in a sheet.
 

correct me if i m wrong.....
if this is a worksheet code........
i m going to use this on a large number of diffrent files!!
will it work??

i have no formal training in VBA.....
what i do is write code in modules, and save it as .xla and it seems to work
across files..

"Mike H" wrote:

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

count no. of times a text appears in a sheet.
 

correct me if i m wrong.....
if this is a worksheet code........
i m going to use this on a large number of diffrent files!!
will it work??

i have no formal training in VBA.....
what i do is write code in modules, and save it as .xla and it seems to work
across files..


Also i just came across "countif" function!!
which has a format like....

=COUNTIF(A1:C100,"Joe")
i can use this on a cell in the worksheet, and then pick this value in my
code!!!(i hope this works)

BUT here too the problem is , it counts only if the entire content of the
cell is just "Joe"
it wont count if JOE is a part of larger string...
any idea, if i can use wildcards (*) inside countif function??

anyother workaround would also help.
thanks


"Mike H" wrote:

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

count no. of times a text appears in a sheet.
 

HEY I GOT IT MIKE,
Thanks

"Khaleel" wrote:

correct me if i m wrong.....
if this is a worksheet code........
i m going to use this on a large number of diffrent files!!
will it work??

i have no formal training in VBA.....
what i do is write code in modules, and save it as .xla and it seems to work
across files..


Also i just came across "countif" function!!
which has a format like....

=COUNTIF(A1:C100,"Joe")
i can use this on a cell in the worksheet, and then pick this value in my
code!!!(i hope this works)

BUT here too the problem is , it counts only if the entire content of the
cell is just "Joe"
it wont count if JOE is a part of larger string...
any idea, if i can use wildcards (*) inside countif function??

anyother workaround would also help.
thanks


"Mike H" wrote:

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