Find a Max Date based on a corresponding value VBA

**jimalya** · 05-25-2013, 04:41 PM

crosslinked:
http://www.ozgrid.com/forum/showthread.php?t=178918
http://www.excelforum.com/excel-prog...value-vba.html
http://www.mrexcel.com/forum/newthre...do=newthread&f

Hello everyone,
I'm in the process of trying to figure out how to find the max date of a value in column A. From there I need to link up the price associated with that date. The information I'm working with is structured like this in this image:

MaxDate.PNG
What I want to do using VBA, is to look at the values in column A and for each of them, find the max date or most recent date. Once that is determined, on a new sheet, I want to write the value found in column A in sheet2/ColumnA and the date in column B, and the price in column C. In a nutshell, I'm looking for an effective date for pricing. The example on the bottom is basically what I'd like to extract with code onto another sheet.

I've tried doing it with a pivot table with no success, and I tried a little bit with the scripting.dictionary, but that's kind of confusing to me. If anyone could help with this, it would be much appreciated.

thanks,
jimalya

**marreco** · 05-25-2013, 04:55 PM

hi
try
=MAX(IF(G2=$A$2:$A$11,$B$2:$B$11,""))

Press -> Ctrl + Alt + Enter

**jimalya** · 05-25-2013, 05:21 PM

Hey marreco, I replied a minute ago, but it's not showing up. If it doesn't show up, thanks for your quick reply. I tried your solution and all I got were zeroes. I did remember to CTRL+SHIFT+ENTER. CTRL+ALT+ENTER didn't do anything.

Although I appreciate your reply, I was actually looking for a VBA solution that would write the found values to another worksheet systematically. I have another function that will be reading from that list. Would you happen to know how to help me with that?

thanks
Jimalya