Vlookup Hyperlink

[Faiek]

Hey All,

Is there a way to like "VLOOKUP" and Value and display the hyperlink on that cell? If not could you please provide alternate ways.


Thanks,
Faiek

 

[tweedle]

This requires a UDF, I believe.
Google search: Excel extract Hyperlink Address = 590k hits
MrExcel podcast: http://www.youtube.com/watch?v=l82wlr3kaFo

 

[Faiek]

I Have this code to create a hyperlink through VBA:

Range("F23").Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        "", TextToDisplay:="Drawing Link"

Is there a way to make the address the same address of a hyperlink on another cell dynamically? Like by incorarating VLOOKUP Or Indexing?

 

[tweedle]

This is one method...copies the hyperlink address from H9 to the selected cell.
If you want to place the result in a cell other than 'selection' then provide a Range reference for the anchor parameter.

For Each hl In ActiveSheet.Hyperlinks
    Debug.Print hl.Parent.Address, hl.Address, hl.SubAddress
    If hl.Parent.Address = "$H$9" Then
    ActiveSheet.Hyperlinks.Add anchor:=Selection, Address:= _
        hl.Address, TextToDisplay:=hl.TextToDisplay
    End If
Next

 

[Faiek]

This is one method...copies the hyperlink address from H9 to the selected cell.
If you want to place the result in a cell other than 'selection' then provide a Range reference for the anchor parameter.

For Each hl In ActiveSheet.Hyperlinks
    Debug.Print hl.Parent.Address, hl.Address, hl.SubAddress
    If hl.Parent.Address = "$H$9" Then
    ActiveSheet.Hyperlinks.Add anchor:=Selection, Address:= _
        hl.Address, TextToDisplay:=hl.TextToDisplay
    End If
Next

Thanks Tweedle. Do You Know of a way to define a cell found through the VLOOKUP and replace H9 With that Value. For example I Have a VLOOKUP showing results from a table, when a Value is entered in the cell. It goes to a Table on another sheet and takes the values from the other columns of data in that table. Is there a way or command or something to indentify the cell found through that VLOOKUP (for example A12 was the cell found in the table that matched the search criteria value) and replace the "$H$9" dynamically?

 

[tweedle]

Vlookup won't work in this case because it returns a value, not a cell reference, or anything to build a cell reference from.

So we can use Match to find the value returned from vlookup in the dataset, and build a cell reference from that.

There is probably more elegant method, however this works on my small test scale:

The Call:
Call SetRefHyperlink(Range("Sheet1!D1:D11"),Range("Sheet2!C1:C11"))
Sheet1!D1:D11 contains my vlookup formulas
Sheet2!C1:C11 contains the data the vlookup formula is searching

Dim C, VLookRng, DataRng As Range
Sub SetRefHyperlink(VLookRng As Range, DataRng As Range)
'VLookRng=the Range of cells containing the Vlookup formula
'DataRng is a single column range, assumes the hyperlink to return is in the 1st column
'Loop through Each cell of the Vlookup formulas
For Each C In VLookRng
    'Is it a Vlookup?
    If InStr(1, C.Formula, "vlookup", vbTextCompare) > 0 Then
        'Match the value returned from vlookup against the DataRng
        'Return thr Row
        RefRow = WorksheetFunction.Match(C.Value, DataRng)
        If RefRow <> "" Then
        
        'Loop through the Hyperlinks of the worksheet containing DataRng
        For Each hl In DataRng.Worksheet.Hyperlinks
            'Debug.Print hl.Parent.Address, hl.Address, hl.SubAddress
            
            'The 'Parent" is the reference to the cell containing the hyperlink
            'DataRng.Column references the 1st column of provided DataRng
            'Adjust anchor:=C.Offset(0, 1) to adjust target of where the new hyperlink resides
            'Currently set to create 1 cell to the right of the Vlookup formula
            If hl.Parent.Row = RefRow And hl.Parent.Column = DataRng.Column Then
                ActiveSheet.Hyperlinks.Add _
                anchor:=C.Offset(0, 1), _
                Address:=hl.Address, _
                TextToDisplay:="Link"
            Exit For
            End If
        Next
        End If
    End If
Next C
End Sub

 

[Faiek]

Vlookup won't work in this case because it returns a value, not a cell reference, or anything to build a cell reference from.

So we can use Match to find the value returned from vlookup in the dataset, and build a cell reference from that.

There is probably more elegant method, however this works on my small test scale:

The Call:
Call SetRefHyperlink(Range("Sheet1!D1:D11"),Range("Sheet2!C1:C11"))
Sheet1!D1:D11 contains my vlookup formulas
Sheet2!C1:C11 contains the data the vlookup formula is searching

Dim C, VLookRng, DataRng As Range
Sub SetRefHyperlink(VLookRng As Range, DataRng As Range)
'VLookRng=the Range of cells containing the Vlookup formula
'DataRng is a single column range, assumes the hyperlink to return is in the 1st column
'Loop through Each cell of the Vlookup formulas
For Each C In VLookRng
    'Is it a Vlookup?
    If InStr(1, C.Formula, "vlookup", vbTextCompare) > 0 Then
        'Match the value returned from vlookup against the DataRng
        'Return thr Row
        RefRow = WorksheetFunction.Match(C.Value, DataRng)
        If RefRow <> "" Then
        
        'Loop through the Hyperlinks of the worksheet containing DataRng
        For Each hl In DataRng.Worksheet.Hyperlinks
            'Debug.Print hl.Parent.Address, hl.Address, hl.SubAddress
            
            'The 'Parent" is the reference to the cell containing the hyperlink
            'DataRng.Column references the 1st column of provided DataRng
            'Adjust anchor:=C.Offset(0, 1) to adjust target of where the new hyperlink resides
            'Currently set to create 1 cell to the right of the Vlookup formula
            If hl.Parent.Row = RefRow And hl.Parent.Column = DataRng.Column Then
                ActiveSheet.Hyperlinks.Add _
                anchor:=C.Offset(0, 1), _
                Address:=hl.Address, _
                TextToDisplay:="Link"
            Exit For
            End If
        Next
        End If
    End If
Next C
End Sub

Thanks For All Your Hard Work! One Problem Though, I Put the code like this;

Dim C, VLookRng, DataRng As Range
Sub OptionButton7_Click()
Range("F9").Formula = "=VLookup(C4, Crossreftable, 3, False)"
Range("E4").Formula = "=VLookup(C4, Crossreftable, 2, False)"
 Call SetRefHyperlink(Range("Search!E4"), Range("Cross-ref!A12:A482"))

End Sub

Sub SetRefHyperlink(VLookRng As Range, DataRng As Range)
'VLookRng=the Range of cells containing the Vlookup formula
'DataRng is a single column range, assumes the hyperlink to return is in the 1st column
'Loop through Each cell of the Vlookup formulas
For Each C In VLookRng
    'Is it a Vlookup?
    If InStr(1, C.Formula, "vlookup", vbTextCompare) > 0 Then
        'Match the value returned from vlookup against the DataRng
        'Return thr Row
        RefRow = WorksheetFunction.Match(C.Value, DataRng)
        If RefRow <> "" Then
        
        'Loop through the Hyperlinks of the worksheet containing DataRng
        For Each hl In DataRng.Worksheet.Hyperlinks
            'Debug.Print hl.Parent.Address, hl.Address, hl.SubAddress
            
            'The 'Parent" is the reference to the cell containing the hyperlink
            'DataRng.Column references the 1st column of provided DataRng
            'Adjust anchor:=C.Offset(0, 1) to adjust target of where the new hyperlink resides
            'Currently set to create 1 cell to the right of the Vlookup formula
            If hl.Parent.Row = RefRow And hl.Parent.Column = DataRng.Column Then
                ActiveSheet.Hyperlinks.Add _
                anchor:=Range("H34"), _
                Address:=hl.Address, _
                TextToDisplay:="Link"
            Exit For
            End If
        Next
        End If
    End If
Next C
End Sub

And Now It always give me Run Time Error: 1004 "Range Of Object_Global Failed" Can You Provide an answer to that problem.

Thanks So Much,

Faiek

 

[tweedle]

Change the call a little bit...

Call SetRefHyperlink(Range("'Search'!E4:E5"), Range("'Cross-ref'!A1:A482")) 'Match will be easier to control starting from row 1

 

[kidwispa]

Where are you looking up the hyperlink from? I was given the following formula that could display a working hyperlink from another worksheet...

=HYPERLINK("mailto:"&VLOOKUP("CELL REF","DATA RANGE","COLUMN INDEX",0),VLOOKUP("CELL REF","DATA RANGE","COLUMN INDEX",0))

Replace the cell ref, data range, and column index...