CODEWITHSUNDEEP
excelvbahyperlink
Stliiyone
Stliiyone

Reputation: 13

Excel Visual Basic Change Cell to hyperlink by adding portion of URL

In Column B I have partial links such as B003KIU14O

What I'm trying to achieve is to have it converted to hyperlink and stayed in the same Column B.

My static (first part of link) link is http://www.amazon.com/gp/product/

The complete link looks like this: http://www.amazon.com/gp/product/B003KIU14O

If possible I would love to have the Visual value in Column B to display B003KIU14O and have hyperlink

Upvotes: 0

Views: 4914

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

light the cells you wish to convert and run this tiny macro:

Sub dural()
    Dim r As Range, s As String, DQ As String
    DQ = Chr(34)
    s = "http://www.amazon.com/gp/product/"
    For Each r In Selection
        v = r.Value
        r.Formula = "=HYPERLINK(" & DQ & s & v & DQ & "," & DQ & v & DQ & ")"
    Next r
End Sub

EDIT#1:

To avoid manual selection:

Sub dural()
    Dim r As Range, s As String, DQ As String
    DQ = Chr(34)
    Dim rBig As Range
    s = "http://www.amazon.com/gp/product/"
    Dim N As Long
    N = Cells(Rows.Count, "B").End(xlUp).Row
    Set rBig = Range("B1:B" & N)
    For Each r In rBig
        v = r.Value
        r.Formula = "=HYPERLINK(" & DQ & s & v & DQ & "," & DQ & v & DQ & ")"
    Next r
End Sub

Upvotes: 1

Joe DeRose
Joe DeRose

Reputation: 3558

I don't think you need Visual Basic, if you don't mind the hyperlink appearing in an adjacent cell: Put the following in the cell where you want the hyperlink to appear (assuming your link appears in cell B1):

=HYPERLINK("http://www.amazon.com/gp/product/" & B1, B1)

Then copy down as necessary for other values in column B.

If you want the full hyperlink to appear, just drop the second parameter:

=HYPERLINK("http://www.amazon.com/gp/product/" & B1)

Upvotes: 1

Related Questions