vba help - advanced filter

[Mallesh23]

Hi Team,

I am using advanced filter to filter and extract data. Criteria is Date , Date columns comes in text.
Convert text date into Date , Find greatest date of Column A , and filter via advanced filter.

Macro giving correct output, But I am not sure whether it creates any problem in future.

is find and replace right approach for converting text to Date format?...

Sub ExtractLatestTrade()

Dim dt As Date

Dim rngData As Range
Dim rngCriteria As Range
Dim rngOutput As Range

Dim rngDay1 As Range
Dim rngDay2 As Range

Set rngDay1 = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set rngDay2 = Range("d2", Range("D" & Rows.Count).End(xlUp))

'Convert from string into Date format
With rngDay1
    .Replace What:="/", Replacement:="/", LookAt:=xlPart
    .NumberFormat = "MM/DD/YYYY"
End With

dt = Format(Application.Max(rngDay1), "MM/DD/YYYY")
Range("H2").Value = ">" & dt 'greater than


With rngDay2
    .Replace What:="/", Replacement:="/", LookAt:=xlPart
    .NumberFormat = "MM/DD/YYYY"
End With

'Store all range for advanced filter.
Set rngData = Range("d1").CurrentRegion
Set rngCriteria = Range("H1").CurrentRegion
Set rngOutput = Range("M1:O1")


rngData.AdvancedFilter xlFilterCopy, rngCriteria, rngOutput

End Sub

Book3
	A	B	C	D	E	F	G	H	I	J	K	L
1	TradeDate			TradeDate	Amount	SalesMan		Max Date Column -A		TradeDate	Amount	SalesMan
2	09/06/20			09/04/20	121	Dhoni				09/08/20	368	Yuvraj
3	09/07/20			09/04/20	325	Sachin				09/08/20	202	Virat
4	09/07/20			09/04/20	333	Dhoni				09/08/20	347	Yuvraj
5	09/07/20			09/06/20	477	Brett Lee				09/08/20	216	Brett Lee
6	09/07/20			09/07/20	388	Dhoni				09/08/20	376	Brett Lee
7	09/07/20			09/07/20	380	Sachin				09/08/20	262	Brett Lee
8	09/04/20			09/07/20	155	Sachin
9	09/04/20			09/07/20	320	Brett Lee
10	09/06/20			09/07/20	326	Macgra
11	09/06/20			09/08/20	368	Yuvraj
12				09/08/20	202	Virat
13				09/08/20	347	Yuvraj
14				09/08/20	216	Brett Lee
15				09/08/20	376	Brett Lee
16				09/08/20	262	Brett Lee
Sheet2

Thanks
mg

 

[JLGWhiz]

I don't understnd why you are replacing a character with the same character. Other than that, the NumberFormat should not cause any problems.
You might want to take a look at this: Type Conversion Functions - Visual Basic

 

[Mallesh23]

Hi JLGWhiz,

Thanks for your help, I have replaced a charecter with same charecter, it helps in converting text to Date
and to extract max date ( through which I can filter data and extract new records greater than max date.)

Thanks
mg

 

[JLGWhiz]

you're welcome,
Regards, JLG