entering complex formula using VBA

[paulsolar]

Hi All

I've been on this for hours and there must be an easier way to do this but I'd like to persevere with the formula route if possible

I'm attempting to enter a formula via vba to add a hyper link, I've been going around in circles for what seems like a lifetime (probably about 2 hours) and cant seem to get it right.

A couple of question

is it possible to do this??
if it is, any ideas where I'm going wrong??

Any help would be appreciated

Kind regards

Paul

Sub create_new_sheet()


Sheet1.Unprotect Password:="password1"


Sheet1.Range("K3") = Sheet2.Range("X7").Value


 Dim strName As String
    Dim wk As Worksheet
    
    strName = Sheet2.Range("X7")
    On Error Resume Next
    Set wk = Worksheets(strName)
    On Error GoTo 0
    If wk Is Nothing Then
        Sheet1.Visible = True
        Sheet1.Copy After:=Worksheets(Worksheets.Count)
NewPageName = Sheet2.Range("X7")
ActiveWindow.ActiveSheet.Name = NewPageName
Sheet16.Visible = False


          Else: MsgBox "Project Already Exists", vbExclamation, "Project Exists"
        Exit Sub
    End If
    
'add hyperlink
Sheet2.Range("X7").Copy
With Sheet17
.Range("B1").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Transpose:=True


[COLOR=#ff0000].Range("B1").Cells(Rows.Count, "B").End(xlUp).Offset(0, 1).Formula = "=HYPERLINK(CELL(""LEFT(CELL(""filename""),FIND(""]"",CELL(""filename""),1))"")&""-'" & "C" & ActiveCell.Row & "'!A1"",""My Name"")"[/COLOR]
End With


'info_to_register
           
End Sub

 

[paulsolar]

Hi

that exactly what I'm after, sorry it took me so long to get there

regards

Paul

 

[shg]

So -- all sorted?

 

[paulsolar]

sorry I still cant get it to work

I'm trying the below

.Range("B1").Cells(Rows.Count, "B").End(xlUp).Offset(0, 1).Formula = "=HYPERLINK(""#" & Sheet2.Range("X7") & "!A1"",""Test Name"")"

It's not throwing any errors and putting the link in the correct place, Sheet2.Range("X7") value is being displayed in the link if you hover over it

 

[paulsolar]

Hi
thanks for sticking with me, I've finally got it working

Here you go

.Range("B1").Cells(Rows.Count, "B").End(xlUp).Offset(0, 1).Formula = "=HYPERLINK(""#'" & Sheet2.Range("X7") & "'!A1"",""Test Name"")"

it just needed the ' at the beginning and end of the sheet name

Phew!!!!

thanks for all the support, I was being led astray by looking at the full link you see when you enter the hyper link manually

thanks again for the assistance

cheers

Paul

 

[paulsolar]

one final question does the # refer to the whole path?

 

[shg]

No, it means "in this document."

You're welcome.

 

[paulsolar]

Thanks for all the help