# Counting exact matches in a string over a defined range

• Greetings,

I'm a bit stumped on integrating all the different coding aspects into a whole, regarding next question:

How many times does "H" (and only exactly the letter "H") appear in Sheets("Code").Range("H6:H45") and put this count into Sheets("Count").Range("C14").

The input for example being;

H6: A
H7: NC, H
...

Count should be 2; not counting the "H" in "Hd".

Maarten

• Re: Counting exact matches in a string over a defined range

HI..

I am pretty sure this will do what you want..

Edit: Some good advice from pike via pm.. thanks mate.. .. looping though the matches is not needed..

So change:

Code
``````For i = 1 To UBound(x)
Set myMatches = .Execute(x(i, 1))
For Each n In myMatches
cnt = cnt + 1
Next n
Next i``````

to be just:

Code
``````For i = 1 To UBound(x)
Set myMatches = .Execute(x(i, 1))
cnt = cnt + myMatches.Count
Next i``````
• Re: Counting exact matches in a string over a defined range

Thanks alot for the quick reply, code works like a charm !
The only minor problem being that "myMatches" wasn't defined as a variable. I solved this by deleting "option explicit", should I Dim the variable as long ?

• Re: Counting exact matches in a string over a defined range

Leave your Option Explicit there.. you could just dim myMatches as Variant

On second thought.. youcan probably do away with the my Matches variable altogether..

So this:

Code
``````Set myMatches = .Execute(x(i, 1))
For Each n In myMatches
cnt = cnt + 1
Next n``````

becomes

Code
``````For i = 1 To UBound(x)
cnt = cnt + .Execute(x(i, 1)).Count
Next i``````
• Re: Counting exact matches in a string over a defined range

All the adjustments still work great, thank you for simplifying the code.
Regarding the "pattern"-code

Code
``.Pattern = "(\b[H]{1}\b)"``

This is a new one for me so i'm trying to learn.
I would also like to search for all: Ma, Mp, Ma-p but not FMa, FMp, FMa-p, ma, mp
Is it possible to achieve this by changing the pattern-code ?
I tried changing the ignorecase to false and the code into below but it doesn't work.

Code
``.Pattern = "(\b[M*]{2}\b)"``
• Re: Counting exact matches in a string over a defined range

Hello,
The Grouping () and character set [] with one value is not required

Code
``.Pattern = "\bH\b"``

and alternation with grouping

Code
``.Pattern = "\bM(a|p|a-p)\b"``
• Re: Counting exact matches in a string over a defined range

Is there a reference guide or website you can refer to regarding the use of .pattern ?

• Re: Counting exact matches in a string over a defined range

There are many websites -google Regular Expressions.

There are differences between languages so have to be a bit careful, but commonalities too.

• Re: Counting exact matches in a string over a defined range

Thanks for the tip !
Stupid question (first time here), where can I mark this thread as solved ?

• Re: Counting exact matches in a string over a defined range

Don't think you can, but there's no need, the thread will eventually drift into oblivion (as long as both of us stop posting!)

• Re: Counting exact matches in a string over a defined range

So the code is have so far is this =

Is it possible to make it only count when another condition is applied ?
I would like it to only add to the counter when in Column "L" of the same rows (N6:N45) there is a "-".

E.g.:

H6 = M ; L6= - -> +1
H7 = M ; L7= + -> no add

• Re: Counting exact matches in a string over a defined range

try...

• Re: Counting exact matches in a string over a defined range

Thx again, this answer made it possible for me to rewrite large parts of the code myself; I'm starting to learn

Another question regarding .Pattern =

Code
``[^-\w]``

What does this part change regarding to the previous

Code
``\b``

?

Code
``.Pattern = "(\b(C){1}\b)"``

The coding above also counts C' , is it possible to make it only count C ?

• Re: Counting exact matches in a string over a defined range

Hey, Thanx for the reference. The most logical formula I can come up with is =

Code
``.Pattern = "\b(C[^\'|F])\b"``

But it still counts C'
I want to count only the letter C by itself and not CF, FC or C'

• Re: Counting exact matches in a string over a defined range

try

• Re: Counting exact matches in a string over a defined range

Code
``cnt = cnt + .Execute(x(i, 1) & Space(1)).Count``

"Space" creates an error: expecting variable or procedure, not a module

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!