Hello,
I want to be able to add hyperlinks to 4 cells in a worksheet in the same row (except for one link) across columns.
Details:
- I want to select a cell in column D and have it add a hyperlink to that active cell called ‘Jump’ that will jump to a cell in column V--still in the same row.
- From column V, I need another hyperlink called ‘Further’ to jump to a cell in column AE--still in the same row. In column V, 1 row beneath, I also need another hyperlink that says 'Home' to jump back to a cell in column A--still in the same row.
- From column AE, I need another hyperlink called ‘End/Home’ to jump back to a cell in column A--still in the same row.
Column A....................Column D..............Column V...............Column AE
(column A is home).........'Jump'...................'Further'..................'End/Home'
(column A is home)......................................'Home'..................................
What I’ve done:
1) I recorded a macro and came up with the code below. It adds the text, 'Jump', 'Further', 'End/Home', and 'Home' where it is supposed to and makes them hyperlinks, as well, but when I click the hyperlinks it says 'Reference Not Valid'. The debugger just highlights the whole row of code but I think the error is in the SubAddress—I don’t know how to structure it using Offset.
2) I’ve also tried code using the Hyperlink function in VBA like below. It adds ‘Jump’ to the active cell as a hyperlink and puts this formula in that cell =HYPERLINK($V$23,"Jump") but it doesn’t jump to the cell V23. I think I need to add #Sheet2! in front of $T$15 but don’t know how to format it.
Also, I don’t know how to keep adding the formula/hyperlink to column V and column AE.
Can someone help me fix one of these codes? I would appreciate the help. Thank you in advance.
(The Office forum couldn't help and it has been a week since I asked.)
I want to be able to add hyperlinks to 4 cells in a worksheet in the same row (except for one link) across columns.
Details:
- I want to select a cell in column D and have it add a hyperlink to that active cell called ‘Jump’ that will jump to a cell in column V--still in the same row.
- From column V, I need another hyperlink called ‘Further’ to jump to a cell in column AE--still in the same row. In column V, 1 row beneath, I also need another hyperlink that says 'Home' to jump back to a cell in column A--still in the same row.
- From column AE, I need another hyperlink called ‘End/Home’ to jump back to a cell in column A--still in the same row.
Column A....................Column D..............Column V...............Column AE
(column A is home).........'Jump'...................'Further'..................'End/Home'
(column A is home)......................................'Home'..................................
What I’ve done:
1) I recorded a macro and came up with the code below. It adds the text, 'Jump', 'Further', 'End/Home', and 'Home' where it is supposed to and makes them hyperlinks, as well, but when I click the hyperlinks it says 'Reference Not Valid'. The debugger just highlights the whole row of code but I think the error is in the SubAddress—I don’t know how to structure it using Offset.
Code:
Sub SHyperlinks()
Dim sht As Worksheet
Set sht = ActiveSheet
'Add hyperlinks starting in active cell on same row across columns D, V, and AE
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
sht.Name & "!ActiveCell.Address.Offset(0, 18)", TextToDisplay:="Jump"
ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell.Offset(0, 18), Address:="", SubAddress:= _
sht.Name & "!ActiveCell.Address.Offset(0, 9)", TextToDisplay:="Further"
''End/Home' jumps back column A to cell in same row
ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell.Offset(0, 27), Address:="", SubAddress:= _
sht.Name & "!ActiveCell.Address.Offset(0, -30)", TextToDisplay:="End/Home"
'Add hyperlink 1 row under 'Further' in column V to go back home to cell in column A in same row
ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell.Offset(1, 18), Address:="", SubAddress:= _
sht.Name & "!ActiveCell.Address.Offset(0, -21)", TextToDisplay:="Home"
End Sub
2) I’ve also tried code using the Hyperlink function in VBA like below. It adds ‘Jump’ to the active cell as a hyperlink and puts this formula in that cell =HYPERLINK($V$23,"Jump") but it doesn’t jump to the cell V23. I think I need to add #Sheet2! in front of $T$15 but don’t know how to format it.
Also, I don’t know how to keep adding the formula/hyperlink to column V and column AE.
Code:
Sub QCHyperlinks()
Dim c As Range
Set c = ActiveCell
With ActiveCell
.Formula = "=HYPERLINK(" & c.Offset(0, 18).Address & "," & """Jump""" & ")"
End With
End Sub
Can someone help me fix one of these codes? I would appreciate the help. Thank you in advance.
(The Office forum couldn't help and it has been a week since I asked.)