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€�.

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".

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".

Range("G1").Value = Application.WorksheetFunction.CountIf(Range(StartC ell,
EndCell), "Gap")


--
__________________________________
HTH

Bob

"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".

Your latest solution works great. I'd like to move one step further and make
the cells evaluated as "Gap" to be easily distinguishable (e.g. filled
"Red"). Additionally, I'd like to set the focus to the location of the the
first cell that reads "Gap".

"Bob Phillips" wrote:

Range("G1").Value = Application.WorksheetFunction.CountIf(Range(StartC ell,
EndCell), "Gap")


--
__________________________________
HTH

Bob

"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".

Set StartCell = Range("A2")
Set EndCell = Range("A10")
With Range(StartCell, EndCell)
If .Cells(1).Value = "Gap" Then
.Cells(1).Select
Else
.Find("Gap").Select
End If
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""gap"""
.FormatConditions(1).Interior.ColorIndex = 3
End With

HTH,
Bernie
MS Excel MVP


"Freddy" wrote in message
...
Your latest solution works great. I'd like to move one step further and make
the cells evaluated as "Gap" to be easily distinguishable (e.g. filled
"Red"). Additionally, I'd like to set the focus to the location of the the
first cell that reads "Gap".

"Bob Phillips" wrote:

Range("G1").Value = Application.WorksheetFunction.CountIf(Range(StartC ell,
EndCell), "Gap")


--
__________________________________
HTH

Bob

"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".

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".

I see the difference between what you indicated and what Bob indicated. Any
progress on making the fill color of the "Gap" cells "Red"?

"Bernie Deitrick" wrote:

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".