Ironman
Well-known Member
- Joined
- Jan 31, 2004
- Messages
- 1,069
- Office Version
- 365
- Platform
- Windows
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.
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!
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.
VBA Code:
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
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!