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

[Guest]

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

 

[Guest]

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

 

[Guest]

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..

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

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

 

[Guest]

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

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

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

 

[Guest]

HEY I GOT IT MIKE,
Thanks

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

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

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