Hyperlink question

[govi]

Hi there,

I have a hyperlink question:

Cell A1 is empty
In cell B1 is a name eg: CNN
In cell C1 is a webadress eg: http://cnn.com

In cell A1 I want the name(B1) containing the webadress from C1

I know I can do it with the Hyperlink function, but I need a macro because I don't want a function in cell A1.

If possible the macro has to loop and look in the other rows to do the same thing till the rows are empty.

Can anyone help?

Thanks a lot!

govi

 

[Hui]

Select the cells in Column A next to the data in Columns B & C then Alt+F8
Run the Insert_Hyperlinks subroutine

Sub Insert_Hyperlinks()
Dim c As Range

For Each c In Selection
  c.Formula = "=HYPERLINK(" + c.Offset(, 2).Address + "," + c.Offset(, 1).Address + ")"
Next
End Sub

 

[govi]

Thanks Hui,

Is it possible to remove the Hyperlink function but still keep all functionality.
I mean the correct name is displayed en the hyperlink is still functioning?

govi

 

[SirJB7]

I know I can do it with the Hyperlink function, but I need a macro because I don't want a function in cell A1.

@Hui
Hi, buddy!
That VBA code will still leave a formula in cell A1. It just changes the method for setting up the cell content.
Regards!

Hi, govi!
Try this:

Option Explicit

Sub Insert_Hyperlinks()
    ' constants
    ' declarations
    Dim c As Range
    ' start
    Set c = [A1]
    ' process
    Do Until c.Offset(0, 2).Value = ""
        c.Offset(0, 2).Copy
        c.PasteSpecial
        c.Hyperlinks(1).TextToDisplay = c.Offset(0, 1).Value
        Set c = c.Offset(1, 0)
    Loop
    ' end
    Application.CutCopyMode = False
    Beep
End Sub

Regards!

 

[govi]

Thanks! That does the trick!

 

[SirJB7]

Hi, govi!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!