Link to variable cells in Excel

Question

I've a single workbook, with two sheets. In Sheet1, I've a link in each row, that would link to Sheet2. In Sheet2, I need to generate a link that would take me back to the same row I clicked on the link. The number of that row is stored in cell A1 of Sheet2 once I click on the link.

For instance, this formula would generate a link that would bring me back to sheet 1, cell 1. However, I want to be able to generate a different row number and not the first row all the time.

Sheet2.Cells(19, 1) = "=HYPERLINK(""#Sheet1!A1"",""CLICK HERE"")"

Thanks.

If you already have the code to put the row number into Sheet2!A1, you don't need VBA for this. Just define a named range, make the named range refer to =INDIRECT("Sheet1!A" & Sheet2!A1), and put that named range as the hyperlink target. — Chel
– Chel, Commented Dec 6, 2013 at 18:26
This is my code for row number, But I couldn't work out your solution, not sure how to do that. : Private Sub Worksheet_SelectionChange(ByVal Target As Range) Sheet2.Cells(1, 1) = Target.Row 'Sheet1.HyperlinkTest2 End Sub Anyhow, the other answer worked out, so thanks. — Amadea
– Amadea, Commented Dec 6, 2013 at 19:33

Sam · Accepted Answer · 2013-12-06 18:36:09Z

Yes, you could set a different range, create a range string and add it into your hyperlink formula.

Sub HyperlinkTest()

Dim cell As Range
Dim rangeString As String

Set cell = Sheet2.Cells(2, 3)

'rangeString = Sheet2!$C$2
 rangeString = cell.Worksheet.Name & "!" & cell.Address


Sheet2.Cells(19, 1) = "=HYPERLINK(""#" & rangeString & """,""CLICK HERE"")"

End Sub

Alternatively, rather than using the Hyperlink function, you could add a hyperlink to you sheet in code.

Here is an example.

Sub HyperlinkTest2()

Dim targetCell As Range, sourceCell As Range
Dim targetRangeString As String

Dim displayText As String

displayText = "Click Me"

Set targetCell = Sheet1.Cells(2, 3) 'where we want the hyperlink to point to
Set sourceCell = Sheet2.Cells(30, 1) 'where we want to put the hyperlink

'target range address including sheet name
targetRangeString = targetCell.Worksheet.Name & "!" & targetCell.Address

'add the hyperlink
Sheet2.Hyperlinks.Add sourceCell, "", targetRangeString, , displayText

End Sub

Collectives™ on Stack Overflow

Link to variable cells in Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related