VB Backward & Forward Loop

[flicky_longcartridge]

Hi Experts,

I'm writing a VB macro that will find the start time and finish time of a job and then work out the time it took to run.

So for example:
D E
<TABLE style="WIDTH: 235pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=313 border=0 x:str><COLGROUP><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2011" width=55><COL style="WIDTH: 194pt; mso-width-source: userset; mso-width-alt: 9435" width=258><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 41pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=55 height=17 x:num="0.41180555555555554">09:53</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 194pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=258 x:str="wsp285A "> wsp285A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="0.41180555555555554">09:53</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:str="wsp285A "> wsp285A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="0.41180555555555554">09:53</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:str="wsp285A "> wsp285A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="0.41180555555555554">09:53</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:str="wsp285A "> wsp285A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="0.41180555555555554">09:53</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:str="wsp285A "> wsp285A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="0.41597222222222219">09:59</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:str="wsp285A "> wsp285A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="0.41597222222222219">09:59</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:str="wsp285A "> wsp285A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="0.42083333333333334">10:06</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:str="wsp285A "> wsp285A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="0.42083333333333334">10:06</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:str="wsp285A "> wsp285A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="0.42569444444444443">10:13</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:str="wsp285A "> wsp285A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="0.42569444444444443">10:13</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:str="wsp285A "> wsp285A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="0.43055555555555558">10:20</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:str="wsp285A "> wsp285A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="0.43055555555555558">10:20</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:str="wsp285A "> wsp285A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="0.43472222222222223">10:26</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:str="wsp285A "> wsp285A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="0.43472222222222223">10:26</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:str="wsp470_cpp_ph2 "> wsp470a </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="0.4368055555555555">10:29</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"> wsp470A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="0.4368055555555555">10:29</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"> wsp470A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="0.4368055555555555">10:29</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:str="wsp470A "> wsp470A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="0.4368055555555555">10:29</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:str="wsp470A "> wsp470A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="0.4368055555555555">10:29 </TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:str="wsp470A "> wsp470A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="0.4368055555555555">10:29</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:str="wsp470A "> wsp470A </TD></TR></TBODY></TABLE>

In the above I want to work our how long job 285A takes, I will need to find the first instance of the job by looping forwards through the data, and the last instance by looping backwards, then taking the last time away from the first.

The only problem is, I have no idea how to write this in VB!!

The job name is always in column E
The time is always in column D
I want to paste the time taken in for arguments sake in cell E1 of the current worksheet

Can anyone help??!!

 

[flicky_longcartridge]

Strange things happened to my internet!! Has this post actually worked?

 

[Jonmo1]

Good news, you can do this without VBA

This will get you the Start Time
=INDEX(D1:D100,MATCH("wsp285A",E1:E100,0))

This will get you the End Time
=LOOKUP(2,1/(E1:E100="wsp285A"),D1:D100)

And you can replace the strings "wsp285A" with cell references containing the job #.

Hope that helps.

 

[flicky_longcartridge]

Hi Jonmo,

Presumably that will work for the first 100 rows and I should increase the size of the range in order to get the rest of the jobs? This Joblog is pretty lengthy!!

 

[Oorang]

Hello,
Here is an example of how you might approach the problem:

Option Explicit
Public Sub Example()
    Const strSeekProduct_c As String = "wsp285a"
    Const lngClmnTimes_c As Long = 4
    Const lngClmnProducts_c As Long = 5
    Dim wsParent As Excel.Worksheet
    Dim rngData As Excel.Range
    Dim lngLwrBnd As Long
    Dim lngUprBnd As Long
    Dim lngRow As Long
    Dim dtFirstTime As Date
    Dim dtLastTime As Date
    Dim strFoundRange1 As String
    Dim strFoundRange2 As String
    Set wsParent = ActiveSheet
    Set rngData = wsParent.Columns(lngClmnProducts_c)
    Set rngData = Intersect(rngData, wsParent.UsedRange)
    lngLwrBnd = rngData.Row
    lngUprBnd = lngLwrBnd + rngData.Rows.Count - 1
    For lngRow = lngLwrBnd To lngUprBnd
        If Trim$(LCase$(wsParent.Cells(lngRow, lngClmnProducts_c).Value)) = strSeekProduct_c Then
            dtFirstTime = wsParent.Cells(lngRow, lngClmnTimes_c).Value
            strFoundRange1 = wsParent.Cells(lngRow, lngClmnTimes_c).Address(False, False)
            Exit For
        End If
    Next
    For lngRow = lngUprBnd To lngLwrBnd Step -1
        If Trim$(LCase$(wsParent.Cells(lngRow, lngClmnProducts_c).Value)) = strSeekProduct_c Then
            dtLastTime = wsParent.Cells(lngRow, lngClmnTimes_c).Value
            strFoundRange2 = wsParent.Cells(lngRow, lngClmnTimes_c).Address(False, False)
            Exit For
        End If
    Next
    MsgBox "Finished scanning for """ & strSeekProduct_c & """" & vbNewLine & vbNewLine & "First time found : " & strFoundRange1 & " = " & dtFirstTime & vbNewLine & "Last Time found : " & strFoundRange2 & " = " & dtLastTime, vbInformation, "Results"
End Sub

 

[Jonmo1]

Just make the ranges longer then you would ever expect it to be..

 

[FundamentalDiscord]

You could create a Dynamic Named Range using OFFSET in lieu of the "hard-coded" range.

 

[Oorang]

Here is a more comment example.

Option Explicit
Public Sub Example()
    Const strSeekProduct_c As String = "wsp285ax" 'Keep this lower case.
    Const lngClmnTimes_c As Long = 4
    Const lngClmnProducts_c As Long = 5
    Dim wsParent As Excel.Worksheet
    Dim rngData As Excel.Range
    Dim lngLwrBnd As Long
    Dim lngUprBnd As Long
    Dim lngRow As Long
    Dim dtFirstTime As Date
    Dim dtLastTime As Date
    Dim strFoundRange1 As String
    Dim strFoundRange2 As String
    'As we refer to the worksheet more than once, put it in an object:
    Set wsParent = ActiveSheet
    'Get the range of the products column:
    Set rngData = wsParent.Columns(lngClmnProducts_c)
    'Shrink the range to just the used area:
    Set rngData = Intersect(rngData, wsParent.UsedRange)
    'Get the top row of products:
    lngLwrBnd = rngData.Row
    'Get the bottom row of products:
    lngUprBnd = lngLwrBnd + rngData.Rows.Count - 1
    'Remove this if you don't use column headers:
    lngLwrBnd = lngLwrBnd + 1
    'Start looping from the top down:
    For lngRow = lngLwrBnd To lngUprBnd
        'Get the value of the current product as specified by row (the looping counter) and
        'column (specified in the constant). Trim to get rid of leading/trailing spaces before
        'the comparison. LCase to make value lower case (so comparison will be case insensitive):
        If LCase$(Trim$(wsParent.Cells(lngRow, lngClmnProducts_c).Value)) = strSeekProduct_c Then
            'If match was found, get the value from the row that had the match, but look in the
            'column that has the times (as specified in the constant above):
            dtFirstTime = wsParent.Cells(lngRow, lngClmnTimes_c).Value
            'Save the range address for future display (optional)"
            strFoundRange1 = wsParent.Cells(lngRow, lngClmnTimes_c).Address(False, False)
            'We found the value, so no need to keep looping.
            Exit For
        End If
    Next
    Select Case True
        'If the row was less than the lower bound than the loop exited before completion.
        '(Meaning a value was found.) Now look for the bottom value.
        Case lngRow < lngUprBnd
            'This works the same as the other loop just from the bottom up.
            For lngRow = lngUprBnd To lngLwrBnd Step -1
                If LCase$(Trim$(wsParent.Cells(lngRow, lngClmnProducts_c).Value)) = strSeekProduct_c Then
                    dtLastTime = wsParent.Cells(lngRow, lngClmnTimes_c).Value
                    strFoundRange2 = wsParent.Cells(lngRow, lngClmnTimes_c).Address(False, False)
                    Exit For
                End If
            Next
            'Verify that the bottom entry is not the same as the top entry:
            If strFoundRange1 = strFoundRange2 Then
                MsgBox "Only one product was found: " & strFoundRange1 & " = " & dtFirstTime, vbInformation, "Results"
            Else
                MsgBox "Finished scanning for """ & strSeekProduct_c & """" & vbNewLine & vbNewLine & "First time found : " & strFoundRange1 & " = " & dtFirstTime & vbNewLine & "Last Time found : " & strFoundRange2 & " = " & dtLastTime, vbInformation, "Results"
            End If
        Case lngRow = lngUprBnd
            'If the row is equal to upper bound, then the first loop found it's
            'first value on the final cell. That means there are no other values:
            MsgBox "Only one product was found: " & strFoundRange1 & " = " & dtFirstTime, vbInformation, "Results"
        Case lngRow > lngUprBnd
            'If the row is greater than the upper bound, then the loop ran all
            'the way through and never exited. That means no values were found:
            MsgBox "Cannot find the product """ & strSeekProduct_c & """.", vbExclamation, "No Results"
    End Select
End Sub

 

[flicky_longcartridge]

Thanks for your help so far guys,

Perhaps if I expand your insight into the issue a little more it will help!

Once I have found the total time taken for the wsp285A job, I then intend to copy the value of time taken over onto a job tracker sheet, simply put this will have an ascending list of dates in column A (From cell A2 downwards) and in column B we will have the time that this job took to run (From B2 down).

I want to copy the value found from my first issue into the last cell with data on, see my bodged example below.
A B
<TABLE style="WIDTH: 235pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=313 border=0 x:str><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; WIDTH: 41pt; BORDER-TOP-COLOR: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" align=right width=55 height=17 x:num="0.41180555555555554">09:53</TD><TD style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; WIDTH: 194pt; BORDER-TOP-COLOR: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" width=258 x:str="wsp285A "> wsp285A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; BORDER-TOP-COLOR: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" align=right height=17 x:num="0.41180555555555554">09:53</TD><TD style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; BORDER-TOP-COLOR: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" x:str="wsp285A "> wsp285A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; BORDER-TOP-COLOR: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" align=right height=17 x:num="0.41180555555555554">09:53</TD><TD style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; BORDER-TOP-COLOR: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" x:str="wsp285A "> wsp285A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; BORDER-TOP-COLOR: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" align=right height=17 x:num="0.41180555555555554">09:53</TD><TD style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; BORDER-TOP-COLOR: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" x:str="wsp285A "> wsp285A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; BORDER-TOP-COLOR: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" align=right height=17 x:num="0.41180555555555554">09:53</TD><TD style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; BORDER-TOP-COLOR: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" x:str="wsp285A "> wsp285A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; BORDER-TOP-COLOR: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" align=right height=17 x:num="0.41597222222222219">09:59</TD><TD style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; BORDER-TOP-COLOR: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" x:str="wsp285A "> wsp285A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; BORDER-TOP-COLOR: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" align=right height=17 x:num="0.41597222222222219">09:59</TD><TD style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; BORDER-TOP-COLOR: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" x:str="wsp285A "> wsp285A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; BORDER-TOP-COLOR: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" align=right height=17 x:num="0.42083333333333334">10:06</TD><TD style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; BORDER-TOP-COLOR: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" x:str="wsp285A "> wsp285A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; BORDER-TOP-COLOR: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" align=right height=17 x:num="0.42083333333333334">10:06</TD><TD style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; BORDER-TOP-COLOR: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" x:str="wsp285A "> wsp285A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; BORDER-TOP-COLOR: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" align=right height=17 x:num="0.42569444444444443">10:13</TD><TD style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; BORDER-TOP-COLOR: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" x:str="wsp285A "> wsp285A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; BORDER-TOP-COLOR: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" align=right height=17 x:num="0.42569444444444443">10:13</TD><TD style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; BORDER-TOP-COLOR: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" x:str="wsp285A "> wsp285A </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; BORDER-TOP-COLOR: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" align=right height=17 x:num="0.43055555555555558">10:20</TD><TD style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; BORDER-TOP-COLOR: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" x:str="wsp285A "> wsp285A
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; BORDER-TOP-COLOR: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" align=right height=17 x:num="0.43055555555555558"></TD><TD style="BORDER-LEFT-COLOR: #e0dfe3; BORDER-BOTTOM-COLOR: #e0dfe3; BORDER-TOP-COLOR: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #e0dfe3" x:str="wsp285A ">

</TD></TR></TBODY></TABLE>
Baring in mind in the above there will be multiple other jobs in column B so it will specifically have to find the first and last instance of wsp285,

once the vb code has aquired this it need to work out the time taken, by subtracting the last instance from the first, this value then needs to be pasted into column B on another sheet called "job tracker" in the first available cell down, see another bodged example below!:

Date:          Time Taken:
15/12/2008   00:07
16/12/2008   00:09
17/12/2008      X

If anyone can help with this, I think it would be a minor miracle!!

 

[Oorang]

Just so you know, you can solve this with fomulas:
To get the first entry, just do a plain old vanilla match/index:

=INDEX(D1:D21,MATCH("wsp285a",$E$1:$E$21,0),1)\

To get the last entry you will need an array formula (Press ctrl-shift enter after typing it)

=INDEX(D1:D21,MAX(--(E1:E21="wsp285a")*(ROW(E1:E21))),1)