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

     

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

      Hi,

       

      You can either depend on the caption name of the sheet or the object name as the below screenshot:

       

       

      Where:

      Sheet3: the object name.

      Working: the caption name.

       

      If you want to use the caption name, use this code:

      criteria = Sheets("Working").Range("A2").Value

       

      And this code for object name:

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

       

      In the code I've suggested, I've used the object name.

       

      Hope that helps

Resources