How do I do this?

[Big UT Fan]

I'd like to take the values (numbers) in a particular column to a hyperlink
in which the number is part of the url. For example, imagine a col of 5
digit #s. In cell A:2 is 12345. I want to change this to a hyperlink to the
url http://www.url.com/id=12345. There has to be a way but I've never done
it. Thanks!

 

[Ross Culver]

Just concatenate the constant with the cell value.

Ross

 

[Big UT Fan]

I tried to use the HYPERLINK function but am getting a circular reference. In
cell B2 I inserted the following funciton:
=HYPERLINK(CONCATENATE("http://www.foo.com/id=",B2))

What am I doing wrong?

 

[Big UT Fan]

Thanks Guys. I'm confident what you put here with the Dim statement will
work but as I've never actually programmed in Excel I'm kind of lost here as
to what to do with this. Any pointers/tips?

 

[Rick Rothstein \(MVP - VB\)]

You are trying to assign to B2 some piece of text and the contents of B2...
you can't do that (hence, the circular reference error). B2 either contains
a value or a formula, it can't have both. Either put the formula in another
cell (say, C2) or you will need to use a macro of some sort if you want to
change the value in place.

Rick

 

[Big UT Fan]

So Rick...Are you saying that I still won't be able to do it using VBA as
suggested by Sandusky in the next reply?

 

[Rick Rothstein \(MVP - VB\)]

Yes you can use VBA (that is what I meant by "or you will need to use a
macro of some sort if you want to change the value in place"). The point of
my response dealt with this statement of yours...

In cell B2 I inserted the following funciton:
=HYPERLINK(CONCATENATE("http://www.foo.com/id=",B2))

Placing a formula referring to B2 in the cell B2 is what caused your
circular error. VBA (or a macro if you will) can write directly into a cell
because it is not "in" the cell itself (like a formula on the worksheet is);
rather, it is something that simply has the ability to act on a worksheet in
whole or in part.

Rick

 

[MacGuy]

try this:

Sub LinkMe()

Range("a2").Select
For x = 1 To ActiveSheet.UsedRange.Rows.Count
tx = "http://www.url.com/id=" & ActiveCell.Value
ActiveCell.Formula = "=hyperlink(""" & tx & """)"
ActiveCell.Offset(1, 0).Select
Next x

End Sub

Given the list of data you want hyperlinked is continuous and it starts in
cell A2. I don't like using Select but it's simple code to get you started.

 

[Charlie]

Do you mean you want to change the cell to become a Hyperlink? So that you
can click on the cell and jump to the link?

Dim Cell As Range

For Each Cell In Range("A1:A9")
ActiveSheet.Hyperlinks.Add _
Anchor:=Cell, _
Address:="http://www.url.com/id=" & Cell.Value, _
SubAddress:="", _
TextToDisplay:="http://www.url.com/id=" & Cell.Value, _
ScreenTip:="Click Here to go to http://www.url.com/id=" & Cell.Value
Next Cell