Hyperlink
Hi
I am trying to write some code to hyperlink between two worksheets.. and in the process have just about gone nuts with a syntax error I a unable to solve. Any suggestions would be appreciated. b = WorksheetB c = WorksheetC x = 4 y = 3 b.Cells(x, 8).Formula = _ "=HYPERLINK(""#""" & "ADDRESS(ROW(" & c.Cells(y, 1).Address "),COLUMN(" & c.Cells(y, 1).Address & "),,,MID(CELL(""filename""," c.Cells(y, 1).Address & "),FIND(""]"",CELL(""filename""," & c.Cells(y 1).Address & "),50)))""" Thanks in advance Pete -- Message posted from http://www.ExcelForum.com |
Hyperlink
Peter, try following.. If you have xl97 to worry about.. you'll have to replace vba's REPLACE with application.substitute Sub foo2() Dim sFML$, b As Range, c As Range, x&, y& sFML = "=HYPERLINK(""#""&CELL(""ADDRESS"",|RNG|)," & vbLf & _ " ""jump to "" &MID(CELL(""ADDRESS"",|RNG|),1 " & vbLf & _ " +FIND(""]"",CELL(""ADDRESS"",|RNG|)),999))" x = 4: y = 3 Set b = Worksheets("WorksheetB").Cells(x, 8) Set c = Worksheets("WorksheetC").Cells(y, 1) b.Formula = Replace(sFML, "|RNG|", c.Address(external:=True)) End Sub -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam PeterW wrote : Hi I am trying to write some code to hyperlink between two worksheets... and in the process have just about gone nuts with a syntax error I am unable to solve. Any suggestions would be appreciated. b = WorksheetB c = WorksheetC x = 4 y = 3 b.Cells(x, 8).Formula = _ "=HYPERLINK(""#""" & "ADDRESS(ROW(" & c.Cells(y, 1).Address & "),COLUMN(" & c.Cells(y, 1).Address & "),,,MID(CELL(""filename""," & c.Cells(y, 1).Address & "),FIND(""]"",CELL(""filename""," & c.Cells(y, 1).Address & "),50)))""" Thanks in advance Peter --- Message posted from http://www.ExcelForum.com/ |
Hyperlink
|
All times are GMT +1. The time now is 06:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com