What Bob said, but if you want the formula and not just the value to appear in the cell, then you
would use

Range("G1").Formula = "=CountIf(" & Range(StartCell, EndCell).Address & ", ""Gap"")"

HTH,
Bernie
MS Excel MVP


"Freddy" wrote in message
...
I've decided to use your suggestion of using the myGap variable. It works
well. Now, I'd like to place the result automatically into cell G1. How do I
do that? Should I use ActiveCell.FormulaR1C1 or a variant there of?

"Bernie Deitrick" wrote:

Lots of ways:

MsgBox Application.WorksheetFunction.CountIf(Range("A:A") , "Gap") & " cells contain ""Gap"""

Or

Set StartCell = Range("A2") 'or other way of identifying cell
Set EndCell = Range("A10")
myGap = Application.WorksheetFunction.CountIf(Range(StartC ell, EndCell), "Gap")
MsgBox myGap & " cells contain ""Gap"""

HTH,
Bernie
MS Excel MVP


"Freddy" wrote in message
...
I would like to know if it is possible to insert a countif formula in a cell
using variables that have been determined by identifying a start cell and an
end cell in a record set.
My goal is to determine, using countif(range,criteria), how many cells in a
range read "Gap".