Hyperlink with dynamical variable in the “middle” of the link

[RaSHkO]

Hello all
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I would like to do the following:

Example:

In cell A1 string (AA0987654)
Now I am trying to make the same cell hyperlink AA0987654 where the link in background should looks like http://example=”value of cell A1”additionalarguments

<o></o>
Is this possible with formula only? The idea behind is just to drag the cell with this formula across the sheet.

<o></o>
Thanks

 

[JamesW]

I'm not sure it is possible as you can't add formula into the Hyperlink "address box".

You can do it with a loop in VBA though (loop through all your rows in column A):

Sub test()
    lRow = Range("A1").End(xlDown).Row
    For i = 2 To lRow
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 2), Address:="http://www." & Cells(i, 1).Value & ".com", TextToDisplay:=Cells(i, 1).Value
    Next
End Sub

The above will loop from row 2 to the last populated row, create a hyperlink based on the text in column A of that row and put the hyperlink in column B.

 

[jkpieterse]

You need the hyperlink worksheet function...

 

[RaSHkO]

Thanks James,
Cheers

 

[RaSHkO]

You need the hyperlink worksheet function...

If I had any idea how to structure the formula I’ll never need to ask here
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Thanks anyway

 

[GlennUK]

Maybe:
=HYPERLINK("http://example="""&A1&"""additionalarguments")

 

[RaSHkO]

Maybe:
=HYPERLINK("http://example="""&A1&"""additionalarguments")

Thanks
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I just realize that the loop I am trying to do is impossible with one row function. The reason is that I am referring to the same cell where I am writing the function. It needs to be VBA.

 

[JamesW]

Hi,

If you are referring to the same cell, try:

Sub test()
    lRow = Range("A1").End(xlDown).Row
    For i = 2 To lRow
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 1), Address:="http://www." & Cells(i, 1).Value & ".com", TextToDisplay:=Cells(i, 1).Value
    Next
End Sub

 

[RaSHkO]

Hi,

If you are referring to the same cell, try:

Sub test()
    lRow = Range("A1").End(xlDown).Row
    For i = 2 To lRow
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 1), Address:="http://www." & Cells(i, 1).Value & ".com", TextToDisplay:=Cells(i, 1).Value
    Next
End Sub

I saw it already. It works very good. Just something additional
What should I edit in order to have this working for multiple columns?
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
thanks

 

[JamesW]

If your data is like this:

A        B        C        D
link1    link2    link3   link4    
AAA    BBB     CCC    DDD
123     112     333    444

Then:

Sub test()
    lRow = Range("A1").End(xlDown).Row
    lCol = Range("A1").End(xlToRight).Column
    For j = 1 To lCol
        For i = 2 To lRow
            ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, j), Address:="http://www." & Cells(i, j).Value & ".com", TextToDisplay:=Cells(i, j).Value
        Next
    Next j
End Sub