Solved

How do I set up a variable cell Hyperlink

Posted on 2015-02-22
7
106 Views
Last Modified: 2016-02-10
In the attached workbook on sheet1 the variable date is in cell M6
The formula to find the address is in cell S6

How do I set up a Hyperlink of VBA code to have focus jump to cell CI19?

The date in M6 will change all the time, so the hyperlink must handle that with a formula....
Hylperlink.xlsm
0
Comment
Question by:DougDodge
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 40625142
Hi,

pls try

Sub Macro()
    Application.Goto Reference:=Range(Range("S6").Value).Address(, , xlR1C1)
End Sub

Open in new window

or as  formula

=HYPERLINK("[Hylperlink.xlsm]Sheet1!"&SUBSTITUTE(ADDRESS(ROW($AP$19:$OP$19),MATCH($M$6,$AP$19:$OP$19,0)+COLUMN(AP19)-1,2),""&ROW($AP$19:$OP$19),19),SUBSTITUTE(ADDRESS(ROW($AP$19:$OP$19),MATCH($M$6,$AP$19:$OP$19,0)+COLUMN(AP19)-1,2),""&ROW($AP$19:$OP$19),19))

Open in new window

Regards
0
 

Author Comment

by:DougDodge
ID: 40625613
Thank you for your reply, but neither of your solutions work.

The macro() gives:
 Run-time error '1004':
Application-defined or object-defined error

The formula tries to search for a workbook on the network and then fails.
0
 

Author Comment

by:DougDodge
ID: 40625623
The Error with the formula:

Opening c:\Users\ddesk\Desktop\Site Progress\Hyperlink.xlsm

Some files can contain viruses or otherwise be harmful to your computer.
It is important to be certain that this file is from a trustworthy source.

Would you like to open this file? OK - CANCEL

Click on OK and get:

An unexpected error has occurred.
0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 40625625
Hi,
is the excel file the same as in the example

Hylperlink.xlsm
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 40625631
pls try

if the Hyperlink file is already open

=HYPERLINK("[Hyperlink.xlsm]Sheet1!"&SUBSTITUTE(ADDRESS(ROW($AP$19:$OP$19),MATCH($M$6,$AP$19:$OP$19,0)+COLUMN(AP19)-1,2),""&ROW($AP$19:$OP$19),19),SUBSTITUTE(ADDRESS(ROW($AP$19:$OP$19),MATCH($M$6,$AP$19:$OP$19,0)+COLUMN(AP19)-1,2),""&ROW($AP$19:$OP$19),19))
0
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40625681
Hi,

with variable file

=HYPERLINK(MID(CELL("filename"), FIND("[",CELL("filename")),100)&"!"&SUBSTITUTE(ADDRESS(ROW($AP$19:$OP$19),MATCH($M$6,$AP$19:$OP$19,0)+COLUMN(AP19)-1,2),""&ROW($AP$19:$OP$19),19),SUBSTITUTE(ADDRESS(ROW($AP$19:$OP$19),MATCH($M$6,$AP$19:$OP$19,0)+COLUMN(AP19)-1,2),""&ROW($AP$19:$OP$19),19))
HylperlinkV1.xlsm
0
 

Author Closing Comment

by:DougDodge
ID: 40625824
Works Perfectly..... Thank you.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question