Using variables within countif
 

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

Using variables within countif
 

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

Using variables within countif
 

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

Using variables within countif
 

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

Using variables within countif
 

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

Using variables within countif
 

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

Using variables within countif
 

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

Using variables within countif
 

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

Using variables within countif
 

I've been using the variables: "searchpoint1" and "searchpoint2" to determine
the range throughout my testing. However, based on your latest sample code, I
do not see where they would come into play. Please advise.

"Bernie Deitrick" wrote:

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

Using variables within countif
 

What kind of variables are they? Strings with cell addresses? Range objects? Code snippets would
help.

HTH,
Bernie
MS Excel MVP


"Freddy" wrote in message
...
I've been using the variables: "searchpoint1" and "searchpoint2" to determine
the range throughout my testing. However, based on your latest sample code, I
do not see where they would come into play. Please advise.

"Bernie Deitrick" wrote:

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

Using variables within countif
 

Essentially they're strings with cell addresses. I use the variables as
follows:
I determine the first row in a record set then use:
searchpoint1 = ActiveCell.Address
I then navigate to the last row of the same record set then use:
searchpoint2 = ActiveCell.Address

I do not dim said variables.

"Bernie Deitrick" wrote:

What kind of variables are they? Strings with cell addresses? Range objects? Code snippets would
help.

HTH,
Bernie
MS Excel MVP


"Freddy" wrote in message
...
I've been using the variables: "searchpoint1" and "searchpoint2" to determine
the range throughout my testing. However, based on your latest sample code, I
do not see where they would come into play. Please advise.

"Bernie Deitrick" wrote:

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

Using variables within countif
 

I am signing out for the day but will check in tomorrow morning, 9/9/08, 8:00
AM EST.

"Bernie Deitrick" wrote:

What kind of variables are they? Strings with cell addresses? Range objects? Code snippets would
help.

HTH,
Bernie
MS Excel MVP


"Freddy" wrote in message
...
I've been using the variables: "searchpoint1" and "searchpoint2" to determine
the range throughout my testing. However, based on your latest sample code, I
do not see where they would come into play. Please advise.

"Bernie Deitrick" wrote:

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

Using variables within countif
 

Freddy,

Then thay are variants which have been set to strings.

Perhaps:

Set StartCell = Range(searchpoint1)
Set EndCell = Range(searchpoint2)

....Rest of code


HTH,
Bernie
MS Excel MVP



"Freddy" wrote in message
...
Essentially they're strings with cell addresses. I use the variables as
follows:
I determine the first row in a record set then use:
searchpoint1 = ActiveCell.Address
I then navigate to the last row of the same record set then use:
searchpoint2 = ActiveCell.Address

I do not dim said variables.

"Bernie Deitrick" wrote:

What kind of variables are they? Strings with cell addresses? Range
objects? Code snippets would
help.

HTH,
Bernie
MS Excel MVP


"Freddy" wrote in message
...
I've been using the variables: "searchpoint1" and "searchpoint2" to
determine
the range throughout my testing. However, based on your latest sample
code, I
do not see where they would come into play. Please advise.

"Bernie Deitrick" wrote:

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

Using variables within countif
 

So far so good. There a are few more enhancements I'd like to implement
related to this same code. The record set I have been referring to spans one
month and contains dates at 15-minute intervals. I'd like the code to
evaluate how many records are expected for a given month (e.g. July 2008
should have 2,976 records. I already have code that evaluates how many
records exist in the current file. But, I need to determine how many records
are missing and write that into a cell. I'd also like to be able to determine
if the record set for the month is leapyear, being that February may contain
either 28 or 29 days.

"Bernie Deitrick" wrote:

Freddy,

Then thay are variants which have been set to strings.

Perhaps:

Set StartCell = Range(searchpoint1)
Set EndCell = Range(searchpoint2)

....Rest of code


HTH,
Bernie
MS Excel MVP



"Freddy" wrote in message
...
Essentially they're strings with cell addresses. I use the variables as
follows:
I determine the first row in a record set then use:
searchpoint1 = ActiveCell.Address
I then navigate to the last row of the same record set then use:
searchpoint2 = ActiveCell.Address

I do not dim said variables.

"Bernie Deitrick" wrote:

What kind of variables are they? Strings with cell addresses? Range
objects? Code snippets would
help.

HTH,
Bernie
MS Excel MVP


"Freddy" wrote in message
...
I've been using the variables: "searchpoint1" and "searchpoint2" to
determine
the range throughout my testing. However, based on your latest sample
code, I
do not see where they would come into play. Please advise.

"Bernie Deitrick" wrote:

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

Using variables within countif
 

Freddy,

For a given month, there would be 4 * 24 * numbers of days in the month. And you can get the days by
getting the day of the 0th day of the next month....

Dim myD As Date
Dim myDC As Integer
Dim myPC As Integer

myD = DateValue("February 1, 2008")
myDC = Day(DateSerial(Year(myD), Month(myD) + 1, 0))
myPC = myDC * 4 * 24

MsgBox "The month " & Format(myD, "mmmm, yyyy") & _
" has " & myDC & " days and " & myPC & " periods."


--
HTH,
Bernie
MS Excel MVP


"Freddy" wrote in message
...
So far so good. There a are few more enhancements I'd like to implement
related to this same code. The record set I have been referring to spans one
month and contains dates at 15-minute intervals. I'd like the code to
evaluate how many records are expected for a given month (e.g. July 2008
should have 2,976 records. I already have code that evaluates how many
records exist in the current file. But, I need to determine how many records
are missing and write that into a cell. I'd also like to be able to determine
if the record set for the month is leapyear, being that February may contain
either 28 or 29 days.

"Bernie Deitrick" wrote:

Freddy,

Then thay are variants which have been set to strings.

Perhaps:

Set StartCell = Range(searchpoint1)
Set EndCell = Range(searchpoint2)

....Rest of code


HTH,
Bernie
MS Excel MVP



"Freddy" wrote in message
...
Essentially they're strings with cell addresses. I use the variables as
follows:
I determine the first row in a record set then use:
searchpoint1 = ActiveCell.Address
I then navigate to the last row of the same record set then use:
searchpoint2 = ActiveCell.Address

I do not dim said variables.

"Bernie Deitrick" wrote:

What kind of variables are they? Strings with cell addresses? Range
objects? Code snippets would
help.

HTH,
Bernie
MS Excel MVP


"Freddy" wrote in message
...
I've been using the variables: "searchpoint1" and "searchpoint2" to
determine
the range throughout my testing. However, based on your latest sample
code, I
do not see where they would come into play. Please advise.

"Bernie Deitrick" wrote:

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

Using variables within countif
 

Got it. That should do it. You have been very helpful. Thanks.

"Bernie Deitrick" wrote:

Freddy,

For a given month, there would be 4 * 24 * numbers of days in the month. And you can get the days by
getting the day of the 0th day of the next month....

Dim myD As Date
Dim myDC As Integer
Dim myPC As Integer

myD = DateValue("February 1, 2008")
myDC = Day(DateSerial(Year(myD), Month(myD) + 1, 0))
myPC = myDC * 4 * 24

MsgBox "The month " & Format(myD, "mmmm, yyyy") & _
" has " & myDC & " days and " & myPC & " periods."


--
HTH,
Bernie
MS Excel MVP


"Freddy" wrote in message
...
So far so good. There a are few more enhancements I'd like to implement
related to this same code. The record set I have been referring to spans one
month and contains dates at 15-minute intervals. I'd like the code to
evaluate how many records are expected for a given month (e.g. July 2008
should have 2,976 records. I already have code that evaluates how many
records exist in the current file. But, I need to determine how many records
are missing and write that into a cell. I'd also like to be able to determine
if the record set for the month is leapyear, being that February may contain
either 28 or 29 days.

"Bernie Deitrick" wrote:

Freddy,

Then thay are variants which have been set to strings.

Perhaps:

Set StartCell = Range(searchpoint1)
Set EndCell = Range(searchpoint2)

....Rest of code


HTH,
Bernie
MS Excel MVP



"Freddy" wrote in message
...
Essentially they're strings with cell addresses. I use the variables as
follows:
I determine the first row in a record set then use:
searchpoint1 = ActiveCell.Address
I then navigate to the last row of the same record set then use:
searchpoint2 = ActiveCell.Address

I do not dim said variables.

"Bernie Deitrick" wrote:

What kind of variables are they? Strings with cell addresses? Range
objects? Code snippets would
help.

HTH,
Bernie
MS Excel MVP


"Freddy" wrote in message
...
I've been using the variables: "searchpoint1" and "searchpoint2" to
determine
the range throughout my testing. However, based on your latest sample
code, I
do not see where they would come into play. Please advise.

"Bernie Deitrick" wrote:

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

Using variables within countif
 

Freddy,

You're quite welcome...

Bernie
MS Excel MVP


"Freddy" wrote in message
...
Got it. That should do it. You have been very helpful. Thanks.

Using variables within countif
 

Bernie, I am returning to continue this thread because I need to know what
adjustment needs to be made to the code (the one shown below) so that it does
not Debug if it does not find the string "Gap".

"Bernie Deitrick" wrote:

What kind of variables are they? Strings with cell addresses? Range objects? Code snippets would
help.

HTH,
Bernie
MS Excel MVP


"Freddy" wrote in message
...
I've been using the variables: "searchpoint1" and "searchpoint2" to determine
the range throughout my testing. However, based on your latest sample code, I
do not see where they would come into play. Please advise.

"Bernie Deitrick" wrote:

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

Using variables within countif
 

Change this

Else
.Find("Gap").Select
End If


Else
IF .Find("Gap") Is Nothing Then Exit Sub
.Find("Gap").Select
End If



--
HTH,
Bernie
MS Excel MVP


"Freddy" wrote in message
...
Bernie, I am returning to continue this thread because I need to know what
adjustment needs to be made to the code (the one shown below) so that it does
not Debug if it does not find the string "Gap".

"Bernie Deitrick" wrote:

What kind of variables are they? Strings with cell addresses? Range objects? Code snippets would
help.

HTH,
Bernie
MS Excel MVP


"Freddy" wrote in message
...
I've been using the variables: "searchpoint1" and "searchpoint2" to determine
the range throughout my testing. However, based on your latest sample code, I
do not see where they would come into play. Please advise.

"Bernie Deitrick" wrote:

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

Using variables within countif
 

Thank you for the solution and the prompt reply. It works.

"Bernie Deitrick" wrote:

Change this

Else
.Find("Gap").Select
End If


Else
IF .Find("Gap") Is Nothing Then Exit Sub
.Find("Gap").Select
End If



--
HTH,
Bernie
MS Excel MVP


"Freddy" wrote in message
...
Bernie, I am returning to continue this thread because I need to know what
adjustment needs to be made to the code (the one shown below) so that it does
not Debug if it does not find the string "Gap".

"Bernie Deitrick" wrote:

What kind of variables are they? Strings with cell addresses? Range objects? Code snippets would
help.

HTH,
Bernie
MS Excel MVP


"Freddy" wrote in message
...
I've been using the variables: "searchpoint1" and "searchpoint2" to determine
the range throughout my testing. However, based on your latest sample code, I
do not see where they would come into play. Please advise.

"Bernie Deitrick" wrote:

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