Find # Days fromCurrent Date in Array...VBA Help

[ExcelKid_10]

Hello-

I'm trying to create a code that will search through an array on a worksheet and find a date that is 7 days prior or closest too from the current. Basically, the sheet contains data that is being imported in from an Access query that gets updated weekly. I've tried creating my own For Each loops and searched the message board but to avail. The structure of my sheet is simple: Column A contains all the dates.

Any thoughts would be much appreciated!

Thanks,

EK

 

[pgc01]

Hi EK

This code selects in column A dates after (today - 7 days).
It assumes that A1 is the header and that the dates are in a contiguous range from A2 down.

I used autofilter. It's very efficient and avoids looping to search the dates.

Then at the end, loops through the dates found and you can insert there your code.

I hope this contributes to the solution of your problem
PGC

Option Explicit

Sub FilterDates()
Dim rAllDates As Range, rDates As Range, rC As Range
    
With Worksheets("Sheet1")
    Set rAllDates = .Range("A2", .Range("A" & ActiveSheet.Rows.Count).End(xlUp))
    .Range("A1").AutoFilter Field:=1, Criteria1:=">=" & Date - 7
    Set rDates = rAllDates.SpecialCells(xlCellTypeVisible)
    .AutoFilterMode = False
End With

For Each rC In rDates
    ' insert your code
    MsgBox rC.Address
Next
End Sub