[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 128
  • Last Modified:

How do I set up a variable cell Hyperlink

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
DougDodge
Asked:
DougDodge
  • 4
  • 3
1 Solution
 
Rgonzo1971Commented:
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
 
DougDodgeAuthor Commented:
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
 
DougDodgeAuthor Commented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rgonzo1971Commented:
Hi,
is the excel file the same as in the example

Hylperlink.xlsm
0
 
Rgonzo1971Commented:
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
 
Rgonzo1971Commented:
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
 
DougDodgeAuthor Commented:
Works Perfectly..... Thank you.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now