Forum Discussion

Anonymous's avatar
Anonymous
Dec 13, 2018

VBA

Hi im trying to make a macro to filter a date column (k1) by a date in a specific cell (a2 in sheet2). I need two vba codes to filter the list to only include dates before the date given in that c...
Need Help VBA coding
Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Dec 14, 2018

Hi,

 

Please try this macro and find it in the attached workbook:

Sub FilterDateBasedOnCellValue()
    
    Dim criteria As Date
    criteria = Sheet1.Range("A2").Value
    
    Sheet2.Range("$K$1:$K$42").AutoFilter Field:=1, Criteria1:="<=" & criteria
    
End Sub
  • ymin2468's avatar
    ymin2468
    Copper Contributor
    Dec 17, 2018

    hi thanks for your help

    I ran the macro from your workbook and get no results.

    when i open the custom filter box it correctly displays the date and when i press ok it filters perfectly!!??

     

    Before I posted my original message i recorded a macro to filter dates and had the same problem!

     

    please help thank you!

     

    • Haytham Amairah's avatar
      Haytham Amairah
      Silver Contributor
      Dec 17, 2018

      Hi,

       

      The code works for me!

      But it seems a bug related to the regional date format in your PC.

      I notice that you use the UK date format (dd/MM/yyyy).

      I am currently using the US format (MM/dd/yyyy).

       

      When I changed the regional date format in my PC to the UK date format, I got the same problem!

       

      However, the workaround is to change the first line in the macro from this:

      Dim criteria As Date

      To this:

      Dim criteria As Long

       

      Hope that helps

      • ymin2468's avatar
        ymin2468
        Copper Contributor
        Dec 18, 2018

        thanks for your help

        The date is on a sheet called workings

        i changed the code to 

        criteria = workings.Range("A2").Value

         

        i get an error message  424 object required 

         

        Thanks

        Haytham Amairah wrote:

        Hi,

         

        The code works for me!

        But it seems a bug related to the regional date format in your PC.

        I notice that you use the UK date format (dd/MM/yyyy).

        I am currently using the US format (MM/dd/yyyy).

         

        When I changed the regional date format in my PC to the UK date format, I got the same problem!

         

        However, the workaround is to change the first line in the macro from this:

        Dim criteria As Date

        To this:

        Dim criteria As Long

         

        Hope that helps

         

Resources