Using variables within countif

[Freddy]

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

 

[Bernie Deitrick]

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(StartCell, EndCell), "Gap")
MsgBox myGap & " cells contain ""Gap"""

HTH,
Bernie
MS Excel MVP

 

[Freddy]

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?

 

[Bob Phillips]

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

 

[Bernie Deitrick]

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]

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

 

[Bernie Deitrick]

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]

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

 

[Freddy]

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]

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

HTH,
Bernie
MS Excel MVP

 

[Freddy]

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.

 

[Freddy]

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

 

[Bernie Deitrick]

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]

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]

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

 

[Freddy]

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

 

[Bernie Deitrick]

Freddy,

You're quite welcome...

Bernie
MS Excel MVP

 

[Freddy]

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]

Change this

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


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

 

[Freddy]

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