How can I open up a hyperlink after my search?

[RonQA]

I wonder if someone can help? I have this code to do a search for a number in my spreadsheet. The number is stored in column A. Once It finds the number I would like it to go across the row it found my number in and open a link to a file in column L.

Can anyone help?

Sub SrchBk()
Dim ws As Worksheet, myvar As String, val1 As Range
Dim val2 As Range, tmp As Range, cnt As Integer
cnt = 0
myvar = InputBox("Please Enter a Search Term?")
If myvar = "" Then Exit Sub
For Each ws In ThisWorkbook.Worksheets
Set val1 = ws.Cells.Find(What:=myvar, LookIn:=xlValues, _
lookat:=xlWhole, MatchCase:=False)
If Not val1 Is Nothing Then
cnt = cnt + 1
Application.Goto val1
Set tmp = val1
again:
If MsgBox("Seach Again?", 4) = vbNo Then Exit Sub
Set val2 = ws.Cells.FindNext(After:=val1)
If val1.Address <> val2.Address And _
tmp.Address <> val2.Address Then
Application.Goto val2
Set val1 = val2
GoTo again
End If
End If
Next ws
If cnt = 0 Then MsgBox "No Matches Found"
End Sub

 

[bartrein]

You can use OFFSET function e.g.

tmp.Offset(0, 11).Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

Assuming "tmp" is your range with a sought number in col A
Offset (0,11) moves your reference from col A to col L (1+11)

hope this helps

 

[RonQA]

Thanks,

That's it. Works great now.