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
    H8: H, Hd, Ad
    ...


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


    Thanks in advance and greetings,


    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


    Thx for the adjustments !
    Is there a reference guide or website you can refer to regarding the use of .pattern ?
    I would like to learn more about the usage of the grouping and character sets.

  • 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


    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!