• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hyperlink question

govi

Member
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
 
Select the cells in Column A next to the data in Columns B & C then Alt+F8
Run the Insert_Hyperlinks subroutine

Code:
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
 
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
 
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:
Code:
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!
 
Hi, govi!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
 
Back
Top