Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Countif And Dates In Vba

1. ## Countif And Dates In Vba

I need a little help with countif and dates in vba. I have a spreadsheet that has different dates in column 3. I need to count the number of times a specific date appears in this column using vba. I'm thinking countif should work, but I can't get it to count the dates.

Excel Video Tutorials / Excel Dashboards Reports

2. Senior Member
Join Date
15th September 2006
Posts
132

## Re: Countif And Dates In Vba

VB:
```ActiveCell.FormulaR1C1 = "=COUNTIF(R[-2]C[2]:R[2]C[2],DATE(2007,1,1))"

```
The formula is read as =COUNTIF("Range",DATE(Year,Month,Day))

Excel Video Tutorials / Excel Dashboards Reports

3. ## Re: Countif And Dates In Vba

I would like to hold the value as a variable in vba:

VB:
```lastrow=worksheetfunction.countif(columns(3),DATE VALUE HERE!)

```
but I cannot get it to return a value.

Excel Video Tutorials / Excel Dashboards Reports

4. Senior Member
Join Date
15th September 2006
Posts
132

## Re: Countif And Dates In Vba

Can you post an example? I don't quite get what you are trying to accomplish now.

Excel Video Tutorials / Excel Dashboards Reports

5. ## Re: Countif And Dates In Vba

See attached. I need a variable to store how many times the date 3/15/2007 is listed in column 1.

Excel Video Tutorials / Excel Dashboards Reports

6. Senior Member
Join Date
15th September 2006
Posts
132

## Re: Countif And Dates In Vba

I guess I just don't get the problem your having. Can't you just put somewhere...

=COUNTIF(A:A,DATE(2007,3,15))

Excel Video Tutorials / Excel Dashboards Reports

7. ## Re: Countif And Dates In Vba

Or in VBA:
VB:
```rngLastRow = Application.CountIf(Range("A:A"), DateValue("3/15/2007"))
' or
rngLastRow = Application.CountIf(Columns(1), DateValue("3/15/2007")
' or
rngLastRow = Application.CountIf(Columns(1), DateSerial(2007,3,15)

```
Last edited by shg; April 20th, 2007 at 03:03.

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno