Count number of cells with Space & "Text" and "text" & space

[drom]

Hi and Thanks in advance!

I have a Column with sentences and/or...

I am Trying to get using VBA how many of the cells in a range of cells (a column, Eg: A5:A1594)

• Have a string ending with: " " & "Hello"
• Have a string starting with: "Hello "
• Conmtaing the string : " Hello "
• Having the string with: "*" & "Hello" & "*" 'So Hello no matter where 'I know how to get this one

I am trying to use:
wCell="Hello"
set Rng=range("A5:A1594")

        xHowManyStartingWithHello = 0:               xHowManyStartingWithHello = Application.WorksheetFunction.WorksheetFunction(Rng, "*" & " " & wCell)   'Should be: 53, I get = 0
        xHowManyEndingWithHello = 0:                 xHowManyEndingWithHello = Application.WorksheetFunction.WorksheetFunction(Rng, wCell & " *")          'Should be: 5,  I get = 0
        xHowManyHelloInTotal = 0:                    xHowManyHelloInTotal = Application.WorksheetFunction.CountIf(Rng, "*" & wCell & "*")                  'Should be: 64, I get = 64
        xHowManyHelloInside = 0:                     xHowManyHelloInside = Application.WorksheetFunction.WorksheetFunction(Rng, "* " &  wCell & " *" )     'Should be: 6,  I get = 0

So I am doing something wrong

I do not want to use for Each cell in Rng.cells ...



But I do not get the right answer

 

[jasonb75]

The lines that don't work are missing Countif, you've typed WorksheetFunction twice instead.

Also, you have the first 2 around the wrong way. It should be "* " & wCell for ending with and wCell & " *" for starting with.

Final tip, for the total you should add the other 3 results together instead of counting again. If you put the spaces into that one it will not work, but without them there is a risk of partial matches being counted in error (for example if wCell contained "apple" then "pineapple" would be counted by your current total line but not by the others).

        xHowManyStartingWithHello = Application.WorksheetFunction.Countif(Rng, wCell & " *")
        xHowManyEndingWithHello = Application.WorksheetFunction.Countif(Rng, "* " & wCell)
        xHowManyHelloInside = Application.WorksheetFunction.Countif(Rng, "* " &  wCell & " *" )
        xHowManyHelloInTotal = xHowManyStartingWithHello +  xHowManyEndingWithHello + xHowManyHelloInside

If that fails then it likely means that wCell also contains spaces which would cause the code to look for non-existent double spaces in the count range.