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

ExcelKid_10

Board Regular
Joined
Mar 17, 2004
Messages
87
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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

Code:
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
 

Forum statistics

Threads
1,141,429
Messages
5,706,406
Members
421,447
Latest member
arthuro2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top