count words in higlighted area

[saban]

Lets say certain part of text is selected via VBA code and now I would like to count words "start" in this highlited area

So if within this higlighted area more than one word "start" is found then exit sub

Thnx

[TonyJollans]

Do you want an actual count, or just to know if there are more than one?

[Jacob Hilderbrand]

This is one method and will let you know how many times the text occurs. You can then use an If statement to have the code exit as needed.


Dim strSearch As String
Dim StrFind As String
Dim Count As Long
Dim n As Long

strSearch = Selection.Range.Text
StrFind = "Start"
n = Len(StrFind)

Count = (Len(strSearch) - Len(Replace(strSearch, StrFind, "", , , vbTextCompare))) / n

MsgBox Count



Basically all we are doing here is determining the difference in the lenths of the original text, and the text after we replace the search term with "". Then divide that number by the length of the search term to get the count.

vbTextCompare makes this a non-case sensitive count.

[saban]

Thnx I will try
I just need to know if in selection word "start" occurs more than once

[TonyJollans]

Hi Jake,

That will count "started", "restart", etc. as well as start.

Find and Replace is a better bet

If YourRange.Find.Execute(FindText:="<[Ss]tart>*<[Ss]tart>", MatchWildcards:=True) Then
' Start occurs more than once in the Range

[Jacob Hilderbrand]

That's true, thanks Tony.