Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 3
Default VBA - Hyperlink Function Combined with Find

Thanks in advance to anyone offering assistance/suggestions to this problem.

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

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

Approach: In a nutshell, I want to be able to use find (or a variation of find) to locate my destination cell (which is a unique value), then pass the location (i.e.: B79) of the destination cell back to the 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:
Range("S6").Select    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _         Cells.Find(What:="All Data, Vendor 1", After:=ActiveCell, LookIn:= _        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _        xlNext, MatchCase:=False, SearchFormat:=False).Activate
Any assistance would be greatly appreciated.

Last edited by Nalij : September 6th 12 at 03:04 PM
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 587
Default VBA - Hyperlink Function Combined with Find

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


Le 2012-09-06 10:01, Nalij a écrit :
Thanks in advance to anyone offering assistance/suggestions to this
problem.

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

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

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

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 587
Default VBA - Hyperlink Function Combined with Find

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 :
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

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 587
Default VBA - Hyperlink Function Combined with Find

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


--
isabelle


Le 2012-09-06 22:34, isabelle a écrit :
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


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 29
Default VBA - Hyperlink Function Combined with Find

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


  #6   Report Post  
Junior Member
 
Posts: 3
Default

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

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

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

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

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

Quote:
Originally Posted by isabelle View Post
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


--
isabelle


Le 2012-09-06 22:34, isabelle a écrit :
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

  #7   Report Post  
Junior Member
 
Posts: 3
Default

So I figured out the solution. Instead of trying to use the find function. I made each header a defined range name then referenced that 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 be able to hyperlink to it.



Quote:
Originally Posted by Nalij View Post
The problem is that I do not know what the range is going to be. I can define the following:

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

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

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

The range is constantly changing and dynamic which is why i cannot hardcode the cell range - I really don't know what it will be.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
More on Combined IF Function Elaine Excel Worksheet Functions 2 April 9th 09 08:54 PM
Find earliest date combined with status Ixtreme Excel Discussion (Misc queries) 2 August 28th 07 04:14 AM
lookup combined with search to find keywords and then assign a cat jalbert1975 Excel Programming 7 September 22nd 06 07:45 PM
vlookup combined with AND-function martho Excel Worksheet Functions 1 January 20th 06 01:49 PM
Find min/max in 2 combined columns Tammi Excel Worksheet Functions 3 June 22nd 05 08:19 PM


All times are GMT +1. The time now is 06:47 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"