creating multiple hyperlinks using a variable

[procadman]

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.)

 

[Fluff]

Hi & welcome to MrExcel.
How about

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

 

[procadman]

Thanks,
That did the trick

 

[procadman]

Quick question:

The current macro creates new text with a new link.

Is there a way to just add links to existing text?

 

[Fluff]

Try

TextToDisplay:=.Range("A" & LCounter).Value

 

[procadman]

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.)

 

[procadman]

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.)

 

[procadman]

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?

 

[procadman]

Thanks, I got it.

With Worksheets("Status2")

It's really obvious once you know it

 

[Fluff]

glad you got it sorted.