Complex Hyperlink Function

[cmeier7]

Hi, in VBA I have

Sheets("" & tab_name & "").Cells(1, 2) = "=HYPERLINK("""" & 'Sheet1'!A2 & ""'Sheet1'!A1"""",""To Main Roll-Up"")"

tab_name is a defined variable.
The code inserts an equation containing a hyperlink function. In 'Sheet1'!A2, I have the current workbook name (ie [Book1]). I believe there is an issue with all of the quotations. Can anyone figure out this puzzle? I've been at it for a while with no luck.

Oh, btw, I get error message '1004' - Application-defined or object-defined error

 

[Norie]

For a start you shouldn't need any quotes within Sheets, that is assuming of course tab_name is a string with a sheet name.

As for the formula, it's hard to tell what the problem is - it probably is the quotes.

What formula do you want to end up with?

 

[cmeier7]

I want to end up with a hyperlink. In Sheet1, Cell A2, I have the current workbook name. So I made refrence to that in the first part of the function. I then want the hyperlink to take me back to Sheet1, Cell A1, which I also have. The last part obviously refers to the name of the hyperlink. I want to be able to paste a formula that creates a hyperlink, but paste using VBA. I have a currently working one in my formula bar-

=HYPERLINK("" & 'Sheet1'!A2 & "'Sheet2'!A1", "FPS")

I am trying to get VBA to put that into a cell for me. Hm, actually writing that may have helped....but go ahead and give me your input if you have the time- thanks!

 

[cmeier7]

.

 

[cmeier7]

Ok, that works in my formula bar- but now the question I have is how to place in a cell the exact text in the previous post using VBA.

 

[Norie]

Try turning on the macro recorder and manually entering the formula you want in the cell.

That should give you a start on the syntax you need.

I think you might be overdosing on quotes.

If you enclose something like 'Sheet2'!A1 in quotes that's exactly what you'll get - you won't get anything that is referring to A1 on Sheet2.

 

[cmeier7]

Ok, I have a clearer problem going on now. This is what I have in code

Sheets("" & tab_name & "").Cells(1, 3) = "=HYPERLINK("" & 'Sheet1'!A2 & " 'Sheet2'!A1","To Reachback")"

I want
"=HYPERLINK("" & 'Sheet1'!A2 & " 'Sheet2'!A1","To Reachback")"
to be a string that VBA inserts into the desired cell. However, VBA is automatically cutting off the string right before 'Sheet2. Do you notice the space between the quotation and the astric? Originally I type it in without the space, but VBA cuts it off at the ' and makes the rest of the string one of those green-texted notes that the program knows to ignore. How do I avoid this and get VBA to enter all of it as one big string?

 

[Norie]

I think you should forget about the formula for a moment.

Did you see what I mentioned was the first problem?

Where do you actually want this formula to go?

What does the correct formula look like when you enter it on a worksheet?

This will put the formula =HYPERLINK(Sheet1!A2 & Sheet2!A1,"To Reachback") into C1 on Sheet1.

 tab_name = "Sheet1"
Sheets(tab_name).Cells(1, 3) = "=HYPERLINK('Sheet1'!A2 & 'Sheet2'!A1,""To Reachback"")"

 

[cmeier7]

Do you mean where do I want the hyperlink or where do I want the hyperlink to take me?

 

[cmeier7]

Ok, for clarification purposes, I want VBA to put this formula (below) into the specific cell. The below formula works. I have the workbook name in Cell A2 of Sheet1. I am wanting to place the hyperlink into Cell C1 of " & tab_name & " which is a String defined variable and changes with every loop in my code.

Sheets(tab_name).Cells(1,3)=HYPERLINK("" & 'Sheet1'!A2 & "Sheet2'!A1", "To Reachback")