entering complex formula using VBA

paulsolar

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

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

shg

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

paulsolar

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

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,833
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
656

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,833
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
656
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,833
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,486
Messages
5,831,970
Members
430,099
Latest member
rdhoy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top