dynamic hyperlink
 

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?

dynamic hyperlink
 

You don't even need that other column of numbers.

Put this in A1:
=hyperlink("http://example.com?id="&row())





Ken wrote:

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?

--

Dave Peterson

dynamic hyperlink
 

But if you did have numbers in column A, you could use this in B1:

=hyperlink("http://example.com?id="&a1)



Dave Peterson wrote:

You don't even need that other column of numbers.

Put this in A1:
=hyperlink("http://example.com?id="&row())

Ken wrote:

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?

--

Dave Peterson

--

Dave Peterson

dynamic hyperlink
 

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 写道：

You don't even need that other column of numbers.

Put this in A1:
=hyperlink("http://example.com?id="&row())





Ken wrote:

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?

--

Dave Peterson

dynamic hyperlink
 

Don't put it in A1.
Put your numbers in column A and put this in B1:

=hyperlink("http://example.com?id="&a1, a1)

(watch your quotes!)

Ken wrote:

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 写道：

You don't even need that other column of numbers.

Put this in A1:
=hyperlink("http://example.com?id="&row())





Ken wrote:

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?

--

Dave Peterson

--

Dave Peterson

dynamic hyperlink
 

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!

Dave Peterson wrote:
Don't put it in A1.
Put your numbers in column A and put this in B1:

=hyperlink("http://example.com?id="&a1, a1)

(watch your quotes!)

Ken wrote:

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 写道：

You don't even need that other column of numbers.

Put this in A1:
=hyperlink("http://example.com?id="&row())





Ken wrote:

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?

--

Dave Peterson

--

Dave Peterson

dynamic hyperlink
 

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

Ken wrote:

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!

Dave Peterson wrote:
Don't put it in A1.
Put your numbers in column A and put this in B1:

=hyperlink("http://example.com?id="&a1, a1)

(watch your quotes!)

Ken wrote:

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 写道：

You don't even need that other column of numbers.

Put this in A1:
=hyperlink("http://example.com?id="&row())





Ken wrote:

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?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

dynamic hyperlink
 

Thanks, I'm new to the marco, but it works fine, thank you.

Dave Peterson wrote:
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

Ken wrote:

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!

Dave Peterson wrote:
Don't put it in A1.
Put your numbers in column A and put this in B1:

=hyperlink("http://example.com?id="&a1, a1)

(watch your quotes!)

Ken wrote:

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 写道：

You don't even need that other column of numbers.

Put this in A1:
=hyperlink("http://example.com?id="&row())





Ken wrote:

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?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson