using countif into a formula

  • Thread starter Thread starter fuzzy
  • Start date Start date
F

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
 
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
 
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
 
Get the address of the cells, first_cell.address and last_cell.address

--

HTH

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

For...Each loop not working 5
Why won't my loop recognize empty cells? 8
get_End question 1
sum a range from a starting point that varies 2
Excel Need Countifs Formula Help 0
VBA Countif across sheets? 5
loop help 6
Excel Use COUNTIF across a range of cells with formulas 0

Back
Top