entering complex formula using VBA

paulsolar

Well-known Member
Joined
Aug 21, 2013
Messages
631
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


Code:
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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
Have you tried recording a macro?
 

paulsolar

Well-known Member
Joined
Aug 21, 2013
Messages
631
Hi

Many thanks for your reply.

All the rest of the macro works fine it is the formula that I can't get right

I think the formula if it were entered directly into the cell should look something like this =HYPERLINK(CELL(LEFT(CELL("filename"),FIND("]",CELL("filename"),1)))&"-'C" & ActiveCell.Row & "'!A1","My Name"). If I could get this correct I can work the rest out.

Regards

Paul
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
What's a formula that you can put in a cell that actually works? That one doesn't.
 

paulsolar

Well-known Member
Joined
Aug 21, 2013
Messages
631

ADVERTISEMENT

Many thanks for your reply.

I've tried every formula I can think of and every combination of formula I've found on the internet and cant seem to get any of them working. I think the solution will have to be a direct vba route

I'm going to have a trawl of the internet and see what I can come up with.

kind regards

Paul
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
Not my question.

I realize you need VBA to apply some customatization. My question is, what is one instance of the correct formula that you can enter from the user interface?

Then, what parts need to be changed by code?
 

paulsolar

Well-known Member
Joined
Aug 21, 2013
Messages
631

ADVERTISEMENT

Hi

I'm really struggling with this

I'm getting very close with the formula now

=HYPERLINK("file///:" & SUBSTITUTE(LEFT(CELL("filename"),FIND("]",CELL("filename"))-1),"[","") & " - '"&"C"& CELL("row")&"'!A1","HELP")

The above almost matches what I get if I enter the hyper link manually, with the exception that C & cell row comes up as C3 for example (C3 contains the sheet name that I want to hyper link to,but it comes into the result as C3 not the cell contents??)

I'm lost now, need to do a bit more reading I think

cheers

Paul
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
Let's try a different tack.

What should the final hyperlink be, if C3 contains "Sheet1"?
 

paulsolar

Well-known Member
Joined
Aug 21, 2013
Messages
631
Hi

Please excuse me if I'm not explaining this properly.

The code at the beginning of this thread creates a new sheet and names it based on the content of X7 in Sheet2 ("New Project") it then copies the content of Sheet2 to the new sheet. It then enters the new sheet name in the next available row in column C of Sheet17 ("select project"), what I'm attempting to do is in column D of Sheet17 in the same row as the new sheet name is have a hyper link to the new sheet Cell A1.

is that a better explanation??

When I started this I thought it would be easiest to use a formula in column D :eek: (now I'm not so sure!)

Kind regards

Paul
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
So the hyperlink is to a place in the same workbook? Ignoring how it gets calculated, then all you need is something like

=HYPERLINK("#Sheet1!A1")

No "File...", no workbook name, .... Correct?
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,910
Messages
5,525,587
Members
409,652
Latest member
strangelyangely

This Week's Hot Topics

Top