dynamic hyperlink

K

Ken

Greetings,

there is a column is number, I want to add a hyperlink to the each
number.
for example,

item A, row 1 is number 1, after the adding the hyperlink to it which
displays 1 but I can click it and link to http://example.com?id=1.
and for row 2, the link will be http://example.com?id=2, and so on.

how to do this?
 
K

Ken

thanks, that makes sense, but I still have a problem while I'm trying
to put this in A1,
=hyperlink("http://example.com?id=&a1, a1), I got an error saying excel
can't calculate a formula.
can you help?

Dave Peterson 写é“：
 
K

Ken

but what I want is just edit column a, not column b, I also don't want
to add new items to exist files, just inserting the hyperlink like I
did by clicking the button "insert hyperlink". The problem is I can't
do this row by row because there are more 300 rows.

please help me!
 
D

Dave Peterson

I find that the =hyperlink() version much better behaved than the
insert|Hyperlink version. I'd use the formula by inserting it into an extra
column. Then hide the original column if it bothered me.

But if you want the other hyperlinks, maybe you can use something like this:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range
Dim wks As Worksheet
Dim myStr As String

Set wks = Worksheets("sheet1")

myStr = "http://example.com?id="

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

For Each myCell In myRng.Cells
With myCell
.Hyperlinks.Add anchor:=.Cells, Address:=myStr & .Value
End With
Next myCell

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
but what I want is just edit column a, not column b, I also don't want
to add new items to exist files, just inserting the hyperlink like I
did by clicking the button "insert hyperlink". The problem is I can't
do this row by row because there are more 300 rows.

please help me!
Click to expand...
 
Top