Search Text and Build Hyperlinks – Excel VBA

Hey Excel users, are you tired of using the find feature in Microsoft Excel in order to search a text but are sick and tired of not getting an automatic link to click on in order to get to that search result?

Well, I am.  I get tired of squinting my eyes to read tiny search results and then clicking one result at a time just to find that particular text.  I want to read text in large size and then have a set of hyperlinks to click on so Excel can take me to that text.  I also want the hit search to display the results corresponding to what I put in the search box to get a better idea of the type of information I am looking for.

 

So, I made my own find feature in Excel by using VBA in the background to search for text in a column and input in the box what I want.  Then a list of hit results are built up in the form of hyperlinks to get a preview of what the sample results look like before I click on that cell.  Getting a preview of them before I click on that hyperlink gives me a greater precision in the type of information I am looking for.

 

Here is a video tutorial on how my own find feature works in a straightforward way of finding text.

 

 

For the VBA codes that run my “Search and Hyperlink” feature, here they are:

***********************************************************************

 

Sub searchandlinkup()
v = 1

candidatelook = InputBox(“Enter the Text You Want To Search”)

rw = ThisWorkbook.ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

ab = selection.Column
cr = ThisWorkbook.ActiveSheet.Cells(Rows.Count, ab).End(xlUp).Row

For dr = 1 To cr
Z = Cells(dr, ab)
L = InStr(1, Z, candidatelook)

If L > 0 Then
With Cells(dr, ab)
.Select
.Font.Bold = “True”
End With

n = Cells(dr, ab)

h = ActiveSheet.Name

kk = selection.Row

ActiveSheet.Hyperlinks.Add _
anchor:=Cells(v, rw + 5), _
Address:=””, _
SubAddress:=”‘” & h & “‘” & “!” & “C” & kk, _
TextToDisplay:=n

v = v + 1

End If
Next dr

Cells(1, rw + 5).Select

End Sub

***********************************************************************

 

For the VBA codes that run my “Go To” Destination feature after you select a hyperlink, these are the set:

***********************************************************************

Sub goback()

p = InputBox(“Enter the Column Letter You Clicked the Hyperlink on”)

gb = selection.Column
fd = selection.Row

Cells(fd, gb).Hyperlinks(1).Follow

k = Cells(fd, gb)

h = selection.Row

md = ActiveSheet.Cells(h, Columns.Count).End(xlToLeft).Column

Y = Cells(fd, gb)

b = ActiveSheet.Name

md = 1 + md

fd = fd

ActiveSheet.Hyperlinks.Add _
anchor:=Cells(h, md), _
Address:=””, _
SubAddress:=”‘” & b & “‘” & “!” & p & fd, _
TextToDisplay:=”Go Back To ” & k

End Sub

***********************************************************************

 

To get video tutorials on my other projects, click on this link:

https://www.youtube.com/channel/UCdhZjVbM-pS3sL1iqtjyDgQ/feed.

 

To get your own custom macros made particularly for you, click on this link:

https://www.fiverr.com/towcal/accelerate-excel-work-through-macros-and-vba.

 

Thanks for reading guys and come back to my website for more future articles.