Solved

How do I set up a variable cell Hyperlink

Posted on 2015-02-22
7
96 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
  • 4
  • 3
7 Comments
 
LVL 49

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 49

Expert Comment

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

Hylperlink.xlsm
0
 
LVL 49

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 49

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Lately there has been a variety of news related to U.S. employment.  Stories about worker productivity, automobile and airline unions, low employment and foreign laborers have frequented the news.  Each story has good and bad attributes we might arg…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

919 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now