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.