creating multiple hyperlinks using a variable

procadman

New Member
Joined
May 25, 2018
Messages
9
Hi,

Need to create a bunch of links in excel.

Got this far but don't know the syntax for using a variable in a cell address...

With Worksheets(1)
Dim LCounter As Integer
For LCounter = 3 To 33
ActiveSheet.Hyperlinks.Add Anchor:=.Range("az,&LCounter"), _
Address:="", SubAddress:=("#STATUS2!a,&LCounter"), _
TextToDisplay:="link 3"
Next LCounter
End With

(Just linking one cell in one sheet to another sheet in the same doc.)

(Macro works fine until I attempt to use the for next loop.)
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi & welcome to MrExcel.
How about
Code:
With Worksheets(1)
   Dim LCounter As Integer
   For LCounter = 3 To 33
      ActiveSheet.Hyperlinks.Add Anchor:=.Range("a" & LCounter), _
      Address:="", SubAddress:=("#STATUS2!a" & LCounter), _
      TextToDisplay:="link " & LCounter
   Next LCounter
End With
 
Upvote 0
Quick question:

The current macro creates new text with a new link.

Is there a way to just add links to existing text?
 
Upvote 0
Try
Code:
TextToDisplay:=.Range("A" & LCounter).Value
 
Upvote 0
Thanks,
I tried leaving the text to display line off, but that didn't work. ;)

(It's been years since I wrote any code other than mapkeys in Proe/Creo.)
 
Upvote 0
Sub Macro5()




'
' Macro5 Macro
'
'
With Worksheets(1)
Dim LCounter As Integer
For LCounter = 3 To 100
ActiveSheet.Hyperlinks.Add Anchor:=.Range("b" & LCounter), _
Address:="", SubAddress:=("#STATUS!a" & LCounter), _
TextToDisplay:="BACK"
Next LCounter
End With


End Sub


Attempting to create links on sheet "status2" back to sheet "status".

Unfortunately this code just overwrites data on "status2".

What am I missing?

(I'm assuming there is a way to tell it to be on a specific sheet.)
 
Upvote 0
Sub Macro5()




'
' Macro5 Macro
'
'
With Worksheets(1)
Dim LCounter As Integer
For LCounter = 3 To 100
ActiveSheet.Hyperlinks.Add Anchor:=.Range("b" & LCounter), _
Address:="", SubAddress:=("#STATUS!a" & LCounter), _
TextToDisplay:="BACK"
Next LCounter
End With


End Sub


Attempting to create links on sheet "status2" back to sheet "status".

Unfortunately this code just overwrites data on "status2".

What am I missing?

(I'm assuming there is a way to tell it to be on a specific sheet.)


do I simply add "Worksheets("Status2").Activate" above the activesheet line?
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top