I Want to Filter my sheet and want all data before current date and # N/A

[Altamash]

Here is my code and i am unable to filter data before current date.
can anyone please tell me how to do that.
I want to put that code in below bold codes.




Sub ReplaceNAwith0InColumnC()
'
' ReplaceNAwith0InColumnC Macro
'

'
Range("C1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$K$1000").AutoFilter Field:=3, Criteria1:="#N/A"
Range("C2").Select
ActiveCell.FormulaR1C1 = "0"
Range("C2").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Selection.End(xlUp).Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter
Range("D1").Select
ActiveSheet.Range("$A$1:$K$1000").AutoFilter Field:=4, Criteria1:="Check"
Range("E1").Select
Selection.AutoFilter
Selection.AutoFilter
Range("D1").Select
ActiveSheet.Range("$A$1:$K$1000").AutoFilter Field:=4, Criteria1:="Check"
Range("F1").Select
ActiveSheet.Range("$A$1:$K$1000").AutoFilter Field:=6, Criteria1:="<" & Now()


End Sub

 

[jorismoerings]

Hi,

Using dates in AutoFilter can be tricky if not using the US date format. One way around this problem is to pass the date to a Long Variable using the DateSerial function.
Try this:

Sub ReplaceNAwith0InColumnC()
'
' ReplaceNAwith0InColumnC Macro
'
Dim dDate As Date
Dim lDate As Long
'
Range("C1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$K$1000").AutoFilter Field:=3, Criteria1:="#N/A"
Range("C2").Select
ActiveCell.FormulaR1C1 = "0"
Range("C2").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Selection.End(xlUp).Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter
Range("D1").Select
ActiveSheet.Range("$A$1:$K$1000").AutoFilter Field:=4, Criteria1:="Check"
Range("E1").Select
Selection.AutoFilter
Selection.AutoFilter
Range("D1").Select
ActiveSheet.Range("$A$1:$K$1000").AutoFilter Field:=4, Criteria1:="Check"
Range("F1").Select

dDate = DateSerial(Year(NOW()), Month(NOW()), Day(NOW()))
lDate = dDate

ActiveSheet.Range("$A$1:$K$1000").AutoFilter Field:=6, Criteria1:="<" & lDate


End Sub

Be aware: i haven't checked the validity of the rest of your code

 

[Altamash]

Hi,
The codes you provided didnt worked.

Below is the sheet and i want to filter column F and want to select #N/A and all dates before current date (current dates changes everyday)
please Help.

my system date format is 29/07/2021

 

[jorismoerings]

Hi,

Oops, my bad.
In the part I added for the date, I've made the mistake to use brackets in the Now function which it shouldn't in VBA.


So this part of the code:

dDate = DateSerial(Year(NOW()), Month(NOW()), Day(NOW()))
lDate = dDate

ActiveSheet.Range("$A$1:$K$1000").AutoFilter Field:=6, Criteria1:="<" & lDate

needs to be changed into:

dDate = DateSerial(Year(Now), Month(Now), Day(Now))
lDate = dDate

ActiveSheet.Range("$A$1:$K$1000").AutoFilter Field:=6, Criteria1:="<" & lDate, Operator:=xlOr, Criteria2:="#N/A"

 

[Altamash]

Hi The codes are working for only #N/A .
Codes are not filtering dates before current date.

Is there any change in codes ?
The snip i have given is same as i am using in excel.

please reply.

 

[jorismoerings]

Hi,

The code provided was tested with true dates.
My gues would be the dates you're using arent true dates but are in fact fields which looks like a date but are in text format.
Could you provide a sample of your data using the XL2BB uitilty provided by MrExcel to rule that out.

More info about the add in: XL2BB - Excel Range to BBCode

 

[Altamash]

Yes, You are right.
i forgot to mention that dates are lookup values and not the true date.

Although i cant download any personal file on my laptop so i cant use XL2BB , cz its company's.

please find below snip, hope this will be helpful.

 

[jorismoerings]

Hi,

In screen shots i can't check the format you're using. My expectation is the value you've highlighted is a text formatted as a date instead of date in a date format.
So Let's check:

1. In the screenshot the cell value as a result of the VLOOKUP is 11/08/21
2. The cell format of the highlighted cell is <General>

can you change the cell format to <Number>

If, as a result of this change the result of the VLOOKUP will change to 44419,00 the result is a true date, if nothing changes the result isn't a true date but is in fact a text value.

I've evaluated my code in a test file which you can download from this location

 

[Altamash]

Hi,

Sorry for the late reply.

I have changed to Numbers but nothing changed to lookupvalue which means its a plain text.
so what next we can d in this situation.

 

[jorismoerings]

Hi,

Ok, so at least we know why the autofilter on date in the VBA didn't work.
There are 2 possibilities and 2 ways to approach changing the Date in plain text to a True Date. The easiest one:

Change the formula in Cell F2 to make sure the formula does not only do the VLOOKUP but also converts the plain text into a date.
You can do this by using 1 of the mentioned options:

1. Use the Datevalue function, like this

   =DATEVALUE(VLOOKUP(A2,RFDIARY!$B$@:$I$1048576,5,0))

   ;
2. Apply some kind of mathematical operation, like this

   VLOOKUP(A2,RFDIARY!$B$@:$I$1048576,5,0)+0

As a important note: if you're going to use the options, make sure all the dates in plain text are formatted exactly like the the system date format.

There are several blogs on-line on the subject of converting text into date. This is one: Excel: convert text to date and number to date