asked on

Using URL from cell as part of a formula in other cell

In an Excel Spreadsheet I have a Column with long Hyperlinks. I wish to create in VBA a macro to place a Hyperlink with a friendly name in a new column.

Cell A2 contains the first hyperlink and I want B2 to display the hyperlink with friendly name, I wish to delete column A next, so I actually don't want a permanent reference to that cell. Instead I want the full hyperlink to be part of the new formula.

I am doing:

Range("A2").Select
cell = ActiveCell.Formula

Range("B2").Select

ActiveCell.Formula = "=HYPERLINK(" & cell & ",""Friendly name"")"

In the Locals window I can see that "cell" is dimensioned as String and is "https://www.com.com"

I keep getting an Application Defined or objet defined error. What am I doing wrong?

Shums Faruk

=HYPERLINK(A2,"Friendly Name")

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Norie

What exactly do you have in A2?

Shums Faruk

If you have range in Column A with URL and Column B with Friendly Name and you want links with Friendly Name in Column C.
You can try below:

Sub ConvertToHyplinks()
Dim Ws As Worksheet
Dim LR As Long
Set Ws = ActiveSheet
LR = Ws.Range("A" & Rows.Count).End(xlUp).Row
Ws.Range("C2:C" & LR).FormulaR1C1 = "=HYPERLINK(RC1,TEXT(RC2,0))"
Ws.Columns(3).AutoFit
End Sub

Open in new window

TFranke

ASKER

Wow, thanks it worked. Could you comment on the solution? It works, I have no idea why!

Thanks!