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.
teachexceltrick.wordpress.com 