Solved

How do I set up a variable cell Hyperlink

Posted on 2015-02-22
7
100 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel VBA 2 31
Excel VBA 10 38
Move data from one sheet to another 11 29
Excel 2010:  Creating a "Yes" or "No" Pop-Up 20 34
Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.

785 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