Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Countif And Dates In Vba

  1. #1
    Join Date
    31st March 2006
    Location
    California, USA
    Posts
    21

    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. #2
    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. #3
    Join Date
    31st March 2006
    Location
    California, USA
    Posts
    21

    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. #4
    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. #5
    Join Date
    31st March 2006
    Location
    California, USA
    Posts
    21

    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.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    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. #7
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    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


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. COUNTIF - Excel 2007 COUNTIF - Excel COUNTIF Example
    By Dave Hawley in forum Excel Formulas (No Questions)
    Replies: 0
    Last Post: July 9th, 2008, 19:48
  2. Use COUNTIF, COUNTA or SUMPRODUCT for dates
    By jeepeebee in forum EXCEL HELP
    Replies: 3
    Last Post: June 26th, 2006, 19:29
  3. Countif and dates
    By juliekel in forum EXCEL HELP
    Replies: 4
    Last Post: June 21st, 2005, 20:59
  4. Using SumIf/CountIf for Dates (To Plot Graphs)
    By limjian85 in forum EXCEL HELP
    Replies: 6
    Last Post: February 24th, 2005, 19:36
  5. COUNTIF function for counting dates within a month
    By Analyst84 in forum EXCEL HELP
    Replies: 1
    Last Post: September 18th, 2004, 02:46

Bookmarks

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