VBA code to calculate number of dates in column that are after todays date

  • Hi


    I need a VBA formula that looks at a column of a large number of dates and calculates the number of dates that are >= todays date.


    I have the excel formula

    Code
    1. =countif(L:L,">="&today())


    But how is that expressed in VBA?


    I also need seperate VBA code to count the dates between TODAY and +90 days time.


    All help appreciated.


    Thanks

  • Re: VBA code to calculate number of dates in column that are after todays date


    OK


    I think I got the countif VBA to work.


    Code
    1. Dim x As Long
    2. Dim z As Date
    3. z = Date
    4. With Sheets("SOPs")
    5. x = Application.WorksheetFunction.CountIf(.Columns("L:L"), ">= & z")
    6. End With
    7. Worksheets("MTD Stats").Range("C19").Value = x


    Appears to work fine.

  • Re: VBA code to calculate number of dates in column that are after todays date


    Hello experts


    Please can you help with my coding.


    I have tested it but it doesn't count properly.


    The first part works but the counting for 'prior to TODAYS date' and 'next 90 days from TODAYS date' doesn't.



    Regards
    Dejamls

  • Re: VBA code to calculate number of dates in column that are after todays date


    Hi


    Still not working.


    The two excel formulas I need converted to VBA if anyone can help, much appreciatted.


    They work but I need them in VBA.


    =COUNTIF(SOPs!L:L, "<=" & TODAY())


    =COUNTIFS(SOPs!L:L,"<="&TODAY()+90,SOPs!L:L,">="&TODAY())

  • Re: VBA code to calculate number of dates in column that are after todays date


    Solution I finally got was inputing formula into cell that I wanted result in and then - clicked record macro - clicked cell - clicked F2 - hit enter - stopped recording - copied recorded VBA into code.


    Works!


    If there is a better way then glad to hear.