VBA Search for most recent occurrence of text string & convert to hyperlink (small amendment needed to code)

[Ironman]

Hi

The below code searches for a short text string in Sheet 2 ("Indoor Bike") and converts all the cells with that string in Sheet 1 ("Training Log") to hyperlinks to Sheet 2.

Sub FindValues()
Application.ScreenUpdating = False
    Dim TL As Worksheet, IB As Worksheet, valueToSearch As String
    Dim i As Long, t As Long, Lr1 As Long, Lr2 As Long
    Set TL = Worksheets("Training Log")
    Set IB = Worksheets("Indoor Bike")
    Lr1 = TL.Cells(Rows.Count, "A").End(xlUp).Row
    Lr2 = IB.Cells(Rows.Count, "A").End(xlUp).Row
    For i = 12 To Lr1
    If InStr(Cells(i, "I"), "INDOOR BIKE SESSION") Then
    valueToSearch = TL.Cells(i, 1)
         For t = 12 To Lr2
            If IB.Cells(t, 1) = valueToSearch Then
               ActiveSheet.Hyperlinks.Add Anchor:=Range("I" & i), Address:="", SubAddress:="'" & "Indoor Bike" & "'!" & Range("J" & t).Address, _
               TextToDisplay:=Cells(i, "I").Text, ScreenTip:="Go To Indoor Bike Sheet, Column J, Row " & t & " for Details"
         Exit For
            End If
         Next t
    End If
    Next i
Application.ScreenUpdating = True
End Sub

What I'd be grateful for is for the above code to be amended so instead of a search through the whole column from Row 12 each time the code is run, I need it to search just for the last text string and convert that to a link.

Please note I didn't write the above code, it was kindly provided here, but I didn't receive a response when I posted this question in that thread (which is around 4 months old) so I'm now posting it as a separate question.

Many thanks!

 

[NoSparks]

Separate the loops and run them backwards (ie:from the bottom up) and exit each For loop upon the first finding of the search value

For i = Lr1 To 12 Step -1
    If InStr(Cells(i, "I"), "INDOOR BIKE SESSION") Then
        valueToSearch = TL.Cells(i, 1)
        Exit For
    End If
Next i

For t = Lr2 To 12 Step -1
    If IB.Cells(t, 1) = valueToSearch Then
        ActiveSheet.Hyperlinks.Add Anchor:=Range("I" & i), Address:="", SubAddress:="'" & "Indoor Bike" & "'!" & Range("J" & t).Address, _
        TextToDisplay:=Cells(i, "I").Text, ScreenTip:="Go To Indoor Bike Sheet, Column J, Row " & t & " for Details"
        Exit For
    End If
Next t

 

[Ironman]

Brilliant! That works perfectly!

Thanks a lot once again for all your time and brainpower NS!