using countif into a formula

[fuzzy]

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

[XPete]

This worked for me
;-)

Dim myRange As Range
Dim Formula As String

Set myRange = Worksheets("Sheet1").Range("A1:C10")

Formula = Application.WorksheetFunction.CountIf(myRange, "P")

MsgBox Formula

[Mike Fogleman]

One method is:

Sub Frmula()
Dim firstcell As String
Dim lastcell As String
firstcell = Range("A1").Address
lastcell = Range("A13").Address
Range("B1").Formula = "=COUNTIF(" & firstcell & ":" & lastcell & ",""p"")"
End Sub

Mike F

"fuzzy" <fuzzy@in.gr> wrote in message
news:1130152803.442839.282160@g14g2000cwa.googlegroups.com...
>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
>

[Bob Phillips]

Get the address of the cells, first_cell.address and last_cell.address

--

HTH

RP
(remove nothere from the email address if mailing direct)


"fuzzy" <fuzzy@in.gr> wrote in message
news:1130152803.442839.282160@g14g2000cwa.googlegroups.com...
> 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
>