VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-301770%22%20slang%3D%22en-US%22%3EVBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-301770%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20im%20trying%20to%20make%20a%20macro%20to%20filter%20a%20date%20column%20(k1)%20by%20a%20date%20in%20a%20specific%20cell%20(a2%20in%20sheet2).%3C%2FP%3E%3CP%3EI%20need%20two%20vba%20codes%3C%2FP%3E%3COL%3E%3CLI%3Eto%20filter%20the%20list%20to%20only%20include%20dates%20before%20the%20date%20given%20in%20that%20cell%3C%2FLI%3E%3CLI%3Eto%20filter%20that%20it%20should%20show%20dates%20equal%20or%20after%20the%20date%20in%20that%20cell%3C%2FLI%3E%3C%2FOL%3E%3CP%3EThank%20you%20if%20you%20can%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-301770%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ENeed%20Help%20VBA%20coding%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-303849%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-303849%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20either%20depend%20on%20the%20caption%20name%20of%20the%20sheet%20or%20the%20object%20name%20as%20the%20below%20screenshot%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20385px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F63157iABC450D84EC2C3B3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22VBA%20Project.png%22%20title%3D%22VBA%20Project.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhere%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3ESheet3%3A%3C%2FSTRONG%3E%20the%20object%20name.%3C%2FP%3E%3CP%3E%3CSTRONG%3EWorking%3A%3C%2FSTRONG%3E%20the%20caption%20name.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20to%20use%20the%20caption%20name%2C%20use%20this%20code%3A%3C%2FP%3E%3CPRE%3Ecriteria%20%3D%20Sheets(%22Working%22).Range(%22A2%22).Value%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20this%20code%20for%20object%20name%3A%3C%2FP%3E%3CPRE%3Ecriteria%20%3D%20Sheet3.Range(%22A2%22).Value%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20code%20I've%20suggested%2C%20I've%20used%20the%20object%20name.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-303265%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-303265%22%20slang%3D%22en-US%22%3E%3CP%3Ethanks%20for%20your%20help%3C%2FP%3E%3CP%3EThe%20date%20is%20on%20a%20sheet%20called%26nbsp%3Bworkings%3C%2FP%3E%3CP%3Ei%20changed%20the%20code%20to%26nbsp%3B%3C%2FP%3E%3CP%3Ecriteria%20%3D%20workings.Range(%22A2%22).Value%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20get%20an%20error%20message%26nbsp%3B%20424%20object%20required%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3Bwrote%3A%3CBR%20%2F%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20code%20works%20for%20me!%3C%2FP%3E%3CP%3EBut%20it%20seems%20a%20bug%20related%20to%20the%20regional%20date%20format%20in%20your%20PC.%3C%2FP%3E%3CP%3EI%20notice%20that%20you%20use%20the%20UK%20date%20format%20(dd%2FMM%2Fyyyy).%3C%2FP%3E%3CP%3EI%20am%20currently%20using%20the%20US%20format%20(MM%2Fdd%2Fyyyy).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20changed%20the%20regional%20date%20format%20in%20my%20PC%20to%20the%20UK%20date%20format%2C%20I%20got%20the%20same%20problem!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20the%20workaround%20is%20to%20change%20the%20first%20line%20in%20the%20macro%20from%20this%3A%3C%2FP%3E%3CPRE%3EDim%20criteria%20As%20Date%3C%2FPRE%3E%3CP%3ETo%20this%3A%3C%2FP%3E%3CPRE%3EDim%20criteria%20As%20Long%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-302865%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-302865%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20code%20works%20for%20me!%3C%2FP%3E%3CP%3EBut%20it%20seems%20a%20bug%20related%20to%20the%20regional%20date%20format%20in%20your%20PC.%3C%2FP%3E%3CP%3EI%20notice%20that%20you%20use%20the%20UK%20date%20format%20(dd%2FMM%2Fyyyy).%3C%2FP%3E%3CP%3EI%20am%20currently%20using%20the%20US%20format%20(MM%2Fdd%2Fyyyy).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20changed%20the%20regional%20date%20format%20in%20my%20PC%20to%20the%20UK%20date%20format%2C%20I%20got%20the%20same%20problem!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20the%20workaround%20is%20to%20change%20the%20first%20line%20in%20the%20macro%20from%20this%3A%3C%2FP%3E%3CPRE%3EDim%20criteria%20As%20Date%3C%2FPRE%3E%3CP%3ETo%20this%3A%3C%2FP%3E%3CPRE%3EDim%20criteria%20As%20Long%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-302838%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-302838%22%20slang%3D%22en-US%22%3E%3CP%3Ehi%20thanks%20for%20your%20help%3C%2FP%3E%3CP%3EI%20ran%20the%20macro%20from%20your%20workbook%20and%20get%20no%20results.%3C%2FP%3E%3CP%3Ewhen%20i%20open%20the%20custom%20filter%20box%20it%20correctly%20displays%20the%20date%20and%20when%20i%20press%20ok%20it%20filters%20perfectly!!%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBefore%20I%20posted%20my%20original%20message%20i%20recorded%20a%20macro%20to%20filter%20dates%20and%20had%20the%20same%20problem!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eplease%20help%20thank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-301860%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-301860%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20this%20macro%20and%20find%20it%20in%20the%20attached%20workbook%3A%3C%2FP%3E%3CPRE%3ESub%20FilterDateBasedOnCellValue()%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20criteria%20As%20Date%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20criteria%20%3D%20Sheet1.Range(%22A2%22).Value%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Sheet2.Range(%22%24K%241%3A%24K%2442%22).AutoFilter%20Field%3A%3D1%2C%20Criteria1%3A%3D%22%26lt%3B%3D%22%20%26amp%3B%20criteria%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
Deleted
Not applicable

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

  1. to filter the list to only include dates before the date given in that cell
  2. to filter that it should show dates equal or after the date in that cell

Thank you if you can help!

5 Replies
Highlighted

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
Highlighted

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!

 

Highlighted

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

Highlighted

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


 

Highlighted

Hi,

 

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

 

VBA Project.png

 

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