VBA Code that will return max date based on multiple criteria

**dubuquer** · 08-14-2013, 12:01 PM

Hello, I have been trying to create some efficiencies in my files using vba and macro's and i am currently in holding pattern. I have a number of columns that all contain different types of data (one column is names, others dates,..etc. However, in column C, i have 3 distinct values (CTR, BTW, and ATY) only. In column E, I have dates with times. I am trying to write a formula/macro that will provide me with the maximum date for (CTR) and the maximum date for (BTW & ATY); the date may change based on the date that the download occurs. I have tried to use the formula max and I have even tried to index the data. However, the answer is always coming back with the max date for the entire row. Plus, to add a complication, the range is variable and depends on the amount of data so its never a fixed range. I really hope someone can help. Thank you in advance for any thoughts on this.

**xladept** · 08-14-2013, 03:38 PM

Can you post a sample file? (Go to advanced and click on the paper clip.)

**dubuquer** · 08-14-2013, 09:10 PM

Hello. Thank you for the quick reply. I am attaching the file. I have simplied the number of columns but the reasoning still holds. I hope this helps.

**xladept** · 08-15-2013, 12:25 AM

I'll take a look tomorrow.

**xladept** · 08-15-2013, 05:16 PM

Hi dubuquer,

Here's a start - see if it's what you wanted:

Please Login or Register  to view this content.

Directions for running the routine(s) just supplied

Copy the code to the clipboard

Press ALT + F11 to open the Visual Basic Editor.

Open a macro-enabled Workbook or save your Workbook As Macro-Enabled

Select “Module” from the Insert menu

Type "Option Explicit" then paste the code under it

And, you should be ready to go

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name

**dubuquer** · 08-15-2013, 11:06 PM

Hello, Good evening. The code worked like charm (much appreciated). Is there a way to restrict the max date for BTW and ATY? What I mean is that I have only two types of data categories 1) The max date for CTR 2) The max date for BTW & ATY. I have a tab for 'time' that shows the end result I am hoping to achieve (new attachement with added tab). I could manipulate the results on the data tab to get there but I would imagine there is a much easier way of doing this with code. By running the code you provided, I would like to copy the final max data in a specific cell for the max of CTR and a specific cell for the max of BTW/ATY (the cells location will not change). Ultimately, I would like to reference that specific cell with the data in as a basis for a vlookup later. I hope this makes sense. Please let me know. Once again. This has been extremely helpful.

**xladept** · 08-16-2013, 01:45 PM

Hi Dubuquer,

It's a little sloppy but it seems to do what you want:

Please Login or Register  to view this content.

**dubuquer** · 08-18-2013, 09:54 PM

Fantastic! This is exactly what I what looking for. Thank you again for the help.

**xladept** · 08-19-2013, 02:13 AM

You're welcome!

VBA Code that will return max date based on multiple criteria

LinkBack

Thread Tools

Rate This Thread

Display

VBA Code that will return max date based on multiple criteria

Re: VBA Code that will return max date based on multiple criteria

Re: VBA Code that will return max date based on multiple criteria

Re: VBA Code that will return max date based on multiple criteria

Re: VBA Code that will return max date based on multiple criteria

Re: VBA Code that will return max date based on multiple criteria

Re: VBA Code that will return max date based on multiple criteria

Re: VBA Code that will return max date based on multiple criteria

Re: VBA Code that will return max date based on multiple criteria

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to return multiple values to a single cell based on multiple criteria

Lookup values in multiple columns based on a date range, return single value per criteria

Excel 2007 : Return Value Based on Multiple Criteria

[SOLVED] Return a value based on multiple criteria

Count Multiple Entries, Return Multiple Rows, Based On 3 Criteria

Bookmarks

Bookmarks

Posting Permissions