Hyperlink in a macro... Not taking my variable...

  • Thread starter Thread starter Jey
  • Start date Start date
J

Jey

Hi !

I got a little problem : I have a table on the first sheet, with numbers
like this :

A
132
145
167
187
198
264
....

These numbers are references to names of other cells on a second sheet. for
example, I have a cell named "id_132", another one "id_145", etc...

What I want is on the first sheet, a column B with a link to the cells in
sheet 2

I tried that :

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
areacount = Selection.Rows.Count
For line = 0 To areacount
Range("B" & line).Select
linktomap = "id_" & Range("A" & line).Value
ActiveCell.FormulaR1C1 = "=HYPERLINK(""linktomap"",""See"")"
Next

The problem is that "linktomap is not interpreted as a variable in the
formula, so it doesn't work. Is there a way to bypass this issue, or to do
it another way ?

Thanks !
Jey
 
I think you have a problem with:

For line = 0 To areacount
Range("B" & line).Select

B0 isn't gonna work.

But this might help:

ActiveCell.FormulaR1C1 = "=HYPERLINK(" & linktomap & ",""See"")"

I think I might approach it this way:

Option Explicit
Sub testme()

Dim linktomap As String
Dim myCell As Range
Dim myRng As Range

With ActiveSheet
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
linktomap = "id_" & myCell.Value
myCell.Offset(0, 1).Formula = "=HYPERLINK(" & linktomap & ",""See"")"
Next myCell
End Sub
 
Thanks for your help. It's better now, but still something is wrong...
The formula are ok in the B column, I know have
=HYPERLINK(id_132;"See"), etc...

BUT when I click on it, nothing is happening... it stays on this cell...

Maybe sthg is wrong with the way I name the cell ?
When I look at the Define Name dialog box, I have id_132, refersTo ="$Q$34"
It should have the name of the sheet in it, shouldn't it ?

Here's how I name the cell :
idgal is a txtbox of a userform...

ActiveCell(trueord, trueabs).Select
nomgal = "id_" & idgal.Value
ActiveWorkbook.Names.Add Name:=nomgal, RefersTo:=Selection.address

Does it seem ok for you ?
 
If you select Q34 of the sheet you want, then use Insert|Name|define, you'll see
something like:

=Sheet1!$Q$34

in the refers to box.

And you could try this:
ActiveWorkbook.Names.Add Name:=nomgal, RefersTo:=Selection

But I'd use:
selection.name = nomgal
 
Thanks again, now names are ok...

And for the hyperlink, I tried another way, and it seems to work better than
the formula :

ActiveSheet.Hyperlinks.Add Anchor:=myCell.Offset(0, 1), address:="",
SubAddress:= _
linktomap, TextToDisplay:="See"

So it works great now ! Thanks
 
Back
Top