How to show cell value and not cell reference in a hyperlink formula

[288enzo]

Hi all,

I'm having trouble with a hyperlink formula.

When I use the below the formula shows as =HYPERLINK("https://webaddress.com/?query=" & G5,G5), but what I need is the value in G5 to show in the formula and not the cell reference.

ActiveSheet.Range("AA5") = "=HYPERLINK(""https://webaddress.com/?query="" & G5,G5)"

As an example, the formula bar for AA5 would show =HYPERLINK("https://webaddress.com/?query=" & 525252,525252), if the value in [G5] was 525252.

I tried adding .Value, but that didn't change the output.

Thank you

 

[rollis13]

You still haven't used my previous suggestion in post #6, it wasn't only to shrink the code, it was to get what you asked for.

 

[288enzo]

You still haven't used my previous suggestion in post #6, it wasn't only to shrink the code, it was to get what you asked for.

Actually, I did try your suggestion and it gives the same result as mine.

 

[rollis13]

Maybe:

Option Explicit
Sub test()
    ActiveSheet.Range("AA5") = "'=HYPERLINK(""https://webaddress.com/?query=" & Range("G5") & "," & Range("G5") & ")"
End Sub

 

[rollis13]

Or:

Sub test()
    ActiveSheet.Range("AA5").Hyperlinks.Add Anchor:=ActiveSheet.Range("AA5"), Address:="https://webaddress.com/?query=" & Range("G5") & "," & Range("G5") & ")", TextToDisplay:=CStr(ActiveSheet.Range("G5"))
    '[AA5].Hyperlinks.Add Anchor:=[AA5], Address:="https://webaddress.com/?query=" & [G5] & "," & [G5] & ")", TextToDisplay:=CStr([G5])
End Sub

Elsewise: I give up, what you are asking for isn't clear to me (or can't be done).