using countif into a formula 1

[fuzzyangel]

I want to put a formula in a cell, to check if a range of cells contains one character.
I think it seems like

ActiveCell.Formula = "=" & "COUNTIF(" & first_cell & ":" & last_cell & ";" & ""P"" & ")"

where first_cell and last_cell are the bounds of a range
and "P" is the character I want to check if it exists

but it does not work

 

[PHV]

Have you tried the macrorecorder when you do what you want manually ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

and what do Fist_Cell and Last_Cell represent ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

Are you looking for instances where P is the only character in the cell, or are you wanting to check if any of the cells in the range contain the character "P"?

If you are after the latter, then try this:
[COLOR=blue white]FirstCell = "$A$1"
LastCell = "$B$10"
[f1].FormulaArray = "=count(SEARCH(""1""," & first_cell & ":" & last_cell & ",1))"
[/color]

Use FIND instead of SEARCH if you want case sensitive results. If you want a true/false returned, then wrap the formula in ISNUMBER like this:
[COLOR=blue white][f1].FormulaArray = "=isnumber(count(SEARCH(""1""," & first_cell & ":" & last_cell & ",1)))"[/color]

You'll notice that the formulas are wrapped in curly braces, {}. If you wanted to enter these Array formulae yourself, you would not include those braces, but would enter the formula using Ctrl + Shift + Enter.

[tt]_____
[blue]-John[/blue][/tt]

"Patriotism is your conviction that this country is superior to all other countries because you were born in it."
[tab]-George Bernard Shaw

Help us help you. Please read FAQ181-2886 before posting.

 

[fuzzyangel]

Thak you!
All posts helped, but what was really helpful was the suggestion to use macro recorder.
It was very difficult to find the right number and use of
all these " characters, and recording really helped.
The right syntax is
ActiveCell.Formula = "=COUNTIF(" & first_cell & ":" & last_cell & ",""P"")