Finding the earliest date w/ matching criteria

Skyhigh · May 23rd 2016

Good morning everyone! I'm having trouble finding a way using strictly VBA to find the earliest scheduled date within a project. Each project is assigned a serial number (EX: U2011). I would like to find the earliest Scheduled Start date of all tasks with serial number "U2011". Any help and recommendations would be greatly appreciated. Thanks!

Results should be:
U2011 - 01/08/2016

[TABLE="width: 500"]

[tr]

[td]

Name

[/td]

[td]

Project

[/td]

[td]

Scheduled Start

[/td]

[td]

Scheduled Finish

[/td]

[td]

Actual Start

[/td]

[td]

Actual Finish

[/td]

[/tr]

[tr]

[td]

John

[/td]

[td]

U2011 - Inspect

[/td]

[td]

5/20/2016

[/td]

[td]

5/25/2016

[/td]

[td]

5/20/2016

[/td]

[td]

05/23/2016

[/td]

[/tr]

[tr]

[td]

Joe

[/td]

[td]

U3022 - Deliver

[/td]

[td]

03/01/2016

[/td]

[td]

3/18/2016

[/td]

[td]

03/10/2016

[/td]

[td]

05/20/2016

[/td]

[/tr]

[tr]

[td]

Sally

[/td]

[td]

U2011 - Package

[/td]

[td]

05/22/2016

[/td]

[td]

05/25/2016

[/td]

[td]

05/25/2016

[/td]

[td]

05/21/2016

[/td]

[/tr]

[tr]

[td]

John

[/td]

[td]

U3022 - Test

[/td]

[td]

02/01/2016

[/td]

[td]

04/01/2016

[/td]

[td]

02/14/2016

[/td]

[td]

05/22/2016

[/td]

[/tr]

[tr]

[td]

Joe

[/td]

[td]

U2011 - Analyze

[/td]

[td]

01/18/2016

[/td]

[td]

05/22/2016

[/td]

[td]

01/22/2016

[/td]

[td]

05/04/2016

[/td]

[/tr]

[/TABLE]

Caleeco · May 31st 2016

Re: Finding the earliest date w/ matching criteria

Hello SkyHigh,

I assume that was a typo in your post, and the resut should be U2011 - 01/18/2016?

If so, give this macro a try. NOTE: please change your the sheet name and column to reference. Currently the macro is looking at Sheet3, and assumes 'Project' Data starts in cell B2.

Code

Sub EarliestSchStart()
Dim data As Range
Dim c As Range
Dim NewDate As Date
Dim OldDate As Date
'Change your Sheet Name and range of "Project" on the next line
Set data = Worksheets("Sheet3").Range("B2", Range("B" & Rows.Count).End(xlUp))
For Each c In data
    Debug.Print c.Value
    If Left(c.Value, 5) = "U2011" Then
        NewDate = c.Offset(0, 1).Value
        If OldDate = Empty Then
            OldDate = NewDate
        ElseIf NewDate < OldDate Then
            OldDate = NewDate
        End If
    End If
Next c


MsgBox "The earliest Scheduled Start Date is:  " & "U2011 - " & OldDate
End Sub

Display More

Let me know how you get on
Thanks

Caleeco

Finding the earliest date w/ matching criteria

Participate now!

Share