Using value from variable in dynamic hyperlink

Question

I have a summary sheet which I use as an index to access several sheets via a hyperlink. I'm extending it so they link directly to the appropriate row within each sheet.

I'm newish to VBA and not sure on the syntax. I'm basically trying to use the value from variable j as the row number in the cell reference. "'!A" is the first part of my cell ref in the code below. I'm then trying to concatenate the string to add j as the row number. Can this be done using the code below? I tried surrounding j with brackets, apostrophe to no avail.

For j = 2 To LastUsedRow

    link = "=Hyperlink(""#'" & (ThisWorkbook.Worksheets(i).Name) & "'!A" & j",""" & (ThisWorkbook.Worksheets(i).Name) & """)"

Many thanks

Comintern · Accepted Answer · 2016-08-05 18:14:25Z

1

Debug.Print is your friend. This is what you get with your current code (assuming j = 1 and ThisWorkbook.Worksheets(i).Name = "Sheet1"):

=Hyperlink("#'Sheet1'!A1,"Sheet1")

The formula should look like this:

=HYPERLINK("#'Sheet2'!A1","Sheet2")

So...add just add the missing ":

link = "=HYPERLINK(""#'" & (ThisWorkbook.Worksheets(i).Name) & _
       "'!A" & j & """,""" & (ThisWorkbook.Worksheets(i).Name) & """)"

answered Aug 5, 2016 at 18:14

Comintern

22.3k6 gold badges36 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Axel Richter · Accepted Answer · 2016-08-05 18:14:24Z

1

The including of double quotes within VBA strings is tricky.

link = "=Hyperlink(""#'" & (ThisWorkbook.Worksheets(i).Name) & "'!A" & j & """,""" & (ThisWorkbook.Worksheets(i).Name) & """)"

will get link as =Hyperlink("#'SheetName'!A2","SheetName")

answered Aug 5, 2016 at 18:14

Axel Richter

62.5k7 gold badges70 silver badges95 bronze badges

Comments

AnthonyT · Accepted Answer · 2016-08-05 18:02:17Z

-1

You need to call on what you want to pull from j, such as: j.Value

answered Aug 5, 2016 at 18:02

AnthonyT

5361 gold badge5 silver badges14 bronze badges

3 Comments

hstay Over a year ago

j is an integer, it doesn't have properties.

AnthonyT Over a year ago

Said who? Looks like j is a range, from 2 to LastUsedRow. So if you want to call the value of j then you do: j.value.

hstay Over a year ago

'Dim j As Range' 'j = 2' Let me know when that could ever work

Collectives™ on Stack Overflow

Using value from variable in dynamic hyperlink

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related