Macro to select hyperlink formula

[Hannah89]

Hi all,

Ive got this macro used to search for a particular code in an excel sheet with about 4000 codes, each assigned a hyperlink.

my goal is to make the user enter the hyperlink automatically in the code upon hitting search.

So far this is what i have:

the problem is that it only works with hyperlinks which are assigned a link (right click assign hyperlink) and not codes which work with a formula for example =HYPERLINK("http://www.google.com", "ABC123")

Sub searchsheet()

'Opens box and ask what do they want to search
searchthis = InputBox("Type in a location keyword.", "Property Search")
'Tells where to search
Columns("A:BO").Select
'and then search in them whatever the user entered:
Selection.Find(What:=searchthis, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext).Activate
'if found go to first found
'if not found show message box saying it cannot be found


End Sub


Sub Macro2()
'
' Macro2 Macro
'

'
Application.Run "Book1!searchsheet"
Selection.Hyperlinks(1).Follow
End Sub

Is there anyone that can help me out?

Thanks

 

[jbeaucaire]

This is how I would "follow" the hyperlink of the active cell's Hyperlink() formula:

Sub FollowHyperlinkFormula()
Dim HLForm As String

HLForm = ActiveCell.Formula
HLForm = Mid(HLForm, InStr(HLForm, "http"), InStrRev(HLForm, """,") - InStr(HLForm, "http"))
ActiveWorkbook.FollowHyperlink Address:=HLForm, NewWindow:=True

End Sub

 

[Hannah89]

Hi Jerry,


Thanks a bunch it worked perfectly