HYPERLINK - How to add a hyperlink to a cell and use the original cell value as the hyperlink text? Arrrgggghhh!!!

[jswhip]

Looping through a worksheet and want to:
1. For each row in Column A, want to add a hyperlink (easy enough)
2. For the hyperlink text, I want to use the value that was originally in column A
3. Storing the original value in a text variable, but can't get the text value to display

I've tried countless iterations of double quotes, ampersands, etc, but without success. Here is what I have so far ("DealName" shows up as the hyperlink text, not the value of the string variable).

Sub test()

Dim x, LastCellRow As Integer
Dim DealName As String

LastCellRow = Cells(65536, 3).End(xlUp).Row

For x = LastCellRow To 3 Step -1

DealName = Cells(x, 1)
Cells(x, 1).FormulaR1C1 = "=HYPERLINK(""https://apps.trepp.com/abpw/w/dealsumm.cgi?cusip=""&RC3,""DealName"")"

Next x

Thanks!

End Sub

 

[Eric W]

Try:

Cells(x, 1).FormulaR1C1 = "=HYPERLINK(""https://apps.trepp.com/abpw/w/dealsumm.cgi?cusip=""&RC3,""" & DealName & """)"

 

[jswhip]

Three double quotes was the solution - thank you!