Excel VBA - Referencing a cell which is hyperlinked

Question

Let me describe my situation,

Basically, there is a cell identified by the name 'test' which contains text called Click Me which holds a hyperlink. I want to reference this cell along with its hyperlink into another cell.

Eg :- Cell Identifier Name: Test Name: Click Me Hyperlink: www.google.com

Here i want to reference Test into another cell along with its hyperlink P.S =Test only gives the value of the Cell Identifier (Click Me) But i want "Click Me with its Hyperlink".

Is this possible?

Thanks in advance

Link

As far as I understand the question, this should be possible. Still, this website if not a free code-writing service. Yet, we are eager to help fellow programmers (and aspirants) with **their** code. Please read the HELP topics for [How do I Ask a Good Question](http://stackoverflow.com/help/how-to-ask), and also how to create a [Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). Afterwards, please update your question with the VBA code you have written thus far in order to complete the task(s) you wish to achieve. — Ralph, Apr 28 '16 at 10:50
Thank you. I'm new to this community. Let me just give a quick look around those links to make this a better questionnaire. — Shashank L, Apr 28 '16 at 11:11
can you explain it a bit? I was not able to get the context. — Rehban Khatri, Apr 28 '16 at 11:16
@RehbanKhatri Please have a look at this link which can give you an insight about the question http://i.stack.imgur.com/kBGt1.jpg — Shashank L, Apr 28 '16 at 11:19
So do you want this referencing of a cell using vba or just a formula? — Rehban Khatri, Apr 28 '16 at 11:20
http://stackoverflow.com/questions/32230657/extract-url-from-excel-hyperlink-formula — Siddharth Rout, Apr 28 '16 at 11:21
@RehbanKhatri Using a formula if possible if not VBA would be good enough. — Shashank L, Apr 28 '16 at 11:25
@SiddharthRout Thanks for the link. Let me refer it and get back. — Shashank L, Apr 28 '16 at 11:27
http://stackoverflow.com/questions/9122046/excel-getting-formula-of-another-cell-in-a-cell-without-vba — Siddharth Rout, Apr 28 '16 at 12:11
Once you have the formula, you can use a combination of Mid,Search to get the hyperlink — Siddharth Rout, Apr 28 '16 at 12:11

score 0 · Answer 1 · answered Apr 28 '16 at 11:36

Try this:

Sub CopyHyperlinks()
    Dim Source As Range
    Dim Destination As Range
    Dim SearchRange As Range

    Set SearchRange = Sheets(1).Range("A1:A5") '---> set your range here
    Set Source = SearchRange(1, 1)
    Set Destination = Sheets(1).Range("B1") '---> range will be copied here

    For Each Source In SearchRange
        If Source.Hyperlinks.Count > 0 Then
            Destination.Hyperlinks.Add Destination, _
                Source.Hyperlinks(1).Address, , , Source.Text
        End If
        Set Destination = Destination(2, 1)
    Next Source
End Sub

Got this from here.

Excel VBA - Referencing a cell which is hyperlinked

1 Answers1