Extract elements from a text string, and return the latest date

[Rob Webster]

if I have a text string, with separators (say commas for now but this is variable)
e.g 1234,1235,1236,1239

and each element refers to a different activity in a programme of construction works (arranged in a list)
A B C D
eg 1234 dig hole Start date end date
1235 seal hole start date end date
1236 paint hole start date end date
1237 leave paint to dry start date end date
etc,

I'm looking for VBA code for a function that can separate out each/every element in the text string and search through the list of tasks to find the activity with the corresponding ID and return the one with the earliest start date or the latest finish date

Ive managed to extract a specific element but need to loop through them all and search a list to return the date, I'm new to this and a long way off. I have a VBA book but cant follow the loop pages.

any advice would be appreciated

 

[GCExcel]

Hi Rob,
Here's a proposal using a custom function.
See attached file for an example on how to use it.

Public Function FindElement(rgList As Range, sTasks As String, bFirst As Boolean) As String
    ' Input parameters :
    ' rgList : range of activities, 4 columns in this order : ID, Task, Start Date, End Date
    ' sText  : string : list of activities separated by comma
    ' bFirst : boolean. if true returns first task.  False returns the last task
    '
    ' Output :
    ' The function returns the task with the earliest start date or latest finish date
   
    Dim i As Integer
    Dim ar
    Dim sTask As String
    Dim dtTemp As Date
   
    ar = rgList.Value
   
    dtTemp = IIf(bFirst, 99999, 0)   'Initialize date
   
    For i = 1 To UBound(ar, 1)
        If InStr(1, sTasks, ar(i, 1)) > 0 Then
            If bFirst Then
                If ar(i, 3) < dtTemp Then
                    dtTemp = ar(i, 3)
                    sTask = ar(i, 2)
                End If
            Else
                If ar(i, 4) > dtTemp Then
                    dtTemp = ar(i, 4)
                    sTask = ar(i, 2)
                End If
            End If
        End If
    Next i
   
    FindElement = sTask

End Function

 

[Rob Webster]

Wow thank you

this is great, I've been messing around for ages trying to set a loop up that runs through the elements! it surpasses what I had in mind, I was looking for it only to return the task id if the latest/earliest item

If I needed to return the task id (first column) instead of the description, (in the cases where descriptions are duplicated (in different sections of the job) how would that be done?