Find Closest Date In Column

**mcjack711** · 04-06-2013, 02:00 PM

Hi all - VBA beginner here. I am using excel to keep track of transactions in my checking account (Col. A = Date, Col. B = Description, Col. C = Amt, Col. D = Balance). Column A then will be in sequential order, but will not contain every date, and will contain the same date multiple times. I'd like to be able to automatically enter my next paycheck. My thought was to find the date of the last "Paycheck" entry, add 14 days to it, and highlight the cell in Col. A where the new date would fall. I've pasted my current code, mostly based on google search results that I tried to modify for my application. Haven't had any luck yet. Any help would be appreciated.

Please Login or Register  to view this content.

**Richard Buttrey** · 04-06-2013, 02:08 PM

Isn't this rather overcomplicating things?

Almost without exception for this type of process, where you want to add a new record to a list, I use a 'New Record' row above the list of records in which I enter the relevant details. Then I click a button which runs a macro that copies this New Record range to the bottom of the list and then sorts the whole list by date (or however else you want to sort it).

**mcjack711** · 04-06-2013, 02:37 PM

That does sound much simpler. I'd like to have it automatically enter the date of the next paycheck for me, which will always be 14 days after the previous one. Any way to simply do that? Thanks...

EDIT: Nevermind - figured out a formula that would automatically enter the correct date. Thought I would document it here in case anyone else can use it. It is an array formula, must enter using CTRL+SHIFT+ENTER:

=INDEX($A$1:$B$9999,MAX(ROW($A$1:$B$9999)*($B$1:$B$9999=""Paycheck"")),1)+14

Find Closest Date In Column

LinkBack

Thread Tools

Rate This Thread

Display

Find Closest Date In Column

Re: Find Closest Date In Column

Re: Find Closest Date In Column

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions