CODEWITHSUNDEEP
vbaexcel
sheela
sheela

Reputation: 29

Filter with loop for Several Dates

I have the following question on using Filter with Loop.

I have several Delivery Dates and a Masterdata of Inventory. I want to display the inventory one day before the delivery date (Column A590). And this has to be done for several dates till the end of the year. I have the basic code for filtering the delivery till the day before the delivery date. But it displays nothing. and I dont know how to put into a loop for consequent days, so that It calculates inventory one day before each delivery date.

Sub DT_Filter()
    ActiveSheet.Range("$G$1:$G$585").AutoFilter Field:=1, Criteria1:="<=" & Range("A590")
End Sub

Upvotes: 1

Views: 173

Answers (1)

Vityata
Vityata

Reputation: 43585

The dates in Excel & VBA are a bit complicated - MSDN Date Systems in Excel. The way you have written the sample, it compares text. Thus, try to convert to a number for a better comparison:

Sub DT_Filter()
    Range("A3:A10").AutoFilter Field:=1, Criteria1:="<=" & CLng(Range("A1"))
End Sub

Thus, if you make a small sample like this one:

enter image description here

You will be able to filter 09-March and 03-March after running the code above like this:

enter image description here

Upvotes: 1

Related Questions