Return max finish date for projects using VBA

[crazymazy]

I have a spreadsheet containing milestones for several different projects. The number of milestones per project varies.

I need to get the max date for each project using VBA.

Simple Example:
'Project Name' is in column A and the 'Scheduled Finish Date' is in column B.

Project X has 5 milestones with the following Scheduled Finish Dates: (8/15, 8/16, 8/17, 8/18, 8/19)
Project Y has 3 milestones with the following Scheduled Finish Dates: (8/20, 8/21, 8/22)
Project Z has 6 milestones with the following Scheduled Finish Dates: (8/23, 8/24, 8/25, 8/26, 8/27, 8/28)


I need the macro to return 8/19 for Project X, 8/22 for Project Y and 8/28 for Project Z.

Anyone know the code that would do this?

Any help is greatly appreciated!

[millz]

I have no idea where you would like to place the dates or whatsoever, and this is assuming the dates are sorting in ascending order. It will show the date in a message box based on selected cell.

Please Login or Register  to view this content.

If you would like the dates displayed in a 3rd column, it's better to use a formula, unless you would like the macro to insert the column.

[crazymazy]

Thanks Millz. I should have mentioned above that I want to get the max finish date for each project and put it in a separate sheet that doesn't have duplicate project names.

Is there a way to write in the max finish date in the sheet with each project only listed once?

Thanks!

[millz]

What duplicate project names? I am lost.

Show a sample data sheet, and as well the 'separate' sheet that holds the results?

[crazymazy]

Millz - see attached very simple example. The first sheet is the data source that would be extracted from a SharePoint list. The second sheet is what I would like the macro to write in. I would like the macro to write in the project name and the max finish date.

I should have mentioned in my first posting that there will be duplicate project names because each project will have milestones.

Sample Max Finish Date.xlsx

[millz]

Try the attached file, I did not add anything fancy to make up the borders though, just purely inserting values.

[crazymazy]

Millz - thank you very much! This is exactly what I needed. This will make things a lot easier for me. Can't thank you enough!