Dec 13 2018 02:23 PM
Dec 13 2018 02:23 PM
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
Thank you if you can help!
Dec 13 2018 08:51 PM
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
Dec 17 2018 08:29 AM
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!
Dec 17 2018 09:02 AM
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
Dec 18 2018 02:50 AM
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 DateTo this:
Dim criteria As Long
Hope that helps
Dec 18 2018 07:41 PM
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