VBA - Hyperlink Function Combined with Find

[Nalij]

Thanks in advance to anyone offering assistance/suggestions to thi
problem

Background: I am trying to embed the find function within my hyperlin
destination address. Since I need to keep the code dynamic because th
location of my information is always changing. I have a worksheet setu
with a number of tables and headers. I have created a table o
hyperlinks to easily access them, however, I cannot get the vba code t
work

Situation: Each header is unique, therefore, I need to combine a searc
function into the standard hyperlink vba

Approach: In a nutshell, I want to be able to use find (or a variatio
of find) to locate my destination cell (which is a unique value), the
pass the location (i.e.: B79) of the destination cell back to th
hyperlink code within the sub-address section

Here is how I have the VBA links structured

Code
-------------------

Range("G6").Select ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ "'Executive Summary - Charts'!A10"

-------------------

I need to somehow combine the anchor with the find function

Code
-------------------

Cells.Find(What:="All Data, Vendor 1", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Activate

-------------------

So that it would ultimately read something like

Code

 

[isabelle]

hi,

assume that the destination address of the link on "sheet1: A1" is "sheet2: A1"

on the following code, if you changing the address of "Worksheets ("Sheet2").Range("A10")"
by your search result, you can go where you want.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If Target.Address = "$A$1" Then
Application.Goto Worksheets("Sheet2").Range("A10")
End If
End Sub

 

[isabelle]

also you can use the same event for multiple links


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Select Case Target.Address(0, 0)
Case Is = "A1": Application.Goto Worksheets("Sheet2").Range("A10")
Case Is = "A2": Application.Goto Worksheets("Sheet2").Range("A20")
Case Is = "A3": Application.Goto Worksheets("Sheet2").Range("A30")
End Select
End Sub


--
isabelle



Le 2012-09-06 20:22, isabelle a écrit :

 

[isabelle]

sorry, it's not "Target.Address(0, 0)" but "Target.Range.Address(0, 0)"


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Select Case Target.Range.Address(0, 0)
Case Is = "A1": Application.Goto Worksheets("Sheet2").Range("A10")
Case Is = "A2": Application.Goto Worksheets("Sheet2").Range("A20")
Case Is = "A3": Application.Goto Worksheets("Sheet2").Range("A30")
End Select
End Sub

 

[benmcclave]

Nalij,

Try this:

Sub AddLink()
Dim sFind As String
Dim rDestCell As Range

sFind = "All Data, Vendor 1"

Set rDestCell = Cells.Find(What:=sFind, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)

If rDestCell Is Nothing Then
MsgBox "'" & sFind & "' not found."
Exit Sub
Else
ActiveSheet.Hyperlinks.Add Anchor:=Range("G6"), Address:="", SubAddress:= _
rDestCell.Address, TextToDisplay:=sFind
End If

End Sub

Hope it helps,

Ben

 

[Nalij]

The problem is that I do not know what the range is going to be. I ca
define the following:

Case Is ="All Data, Vendor 1": Application.Got
Worksheets("Dataset").Range("?????")

That is why i need to combine this with the find function, so that onc
the cell with "All Data, Vendor 1" is identified (say B89) it can pas
that to the vba code, so that it would read:

Case Is ="All Data, Vendor 1": Application.Got
Worksheets("Dataset").Range("B89")

The range is constantly changing and dynamic which is why i canno
hardcode the cell range - I really don't know what it will be.

 

[Nalij]

So I figured out the solution. Instead of trying to use the fin
function. I made each header a defined range name then referenced tha
within the hyperlink


Code
-------------------
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"All_Data_All_Vendors", TextToDisplay:="Vendor1"

-------------------


So now regardless of where the cell is within the worksheet, I will b
able to hyperlink to it.