CODEWITHSUNDEEP
vbaexcel
Amadea
Amadea

Reputation: 51

Link to variable cells in Excel

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.

Upvotes: 0

Views: 2229

Answers (1)

Sam
Sam

Reputation: 7303

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

Upvotes: 2

Related Questions