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/