Trying to get URLs from another sheet using VLOOKUP in VBA

[dagda13]

Hi,

So I have a list of IDs in Sheet1. I am trying to replace them with hyperlinks using the below but I cannot seem to get VLOOKUP to work properly in VBA. I've tried to replace the "URL" string with a VLOOKUP to get the URLs that are on Sheet2, but everything I've tried has resulted in errors. Is there a way to get the Address with a simple VLOOKUP in the loop belew? Any help appreciated!

Sub GetHyperlink()
   For Each xCell In Range("A:A")
      If xCell.Value <> "" Then
        ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:="URL", TextToDisplay:=xCell.Value
      End If
   Next xCell
End Sub

 

[offthelip]

How are the URL stored on sheet2?? are they hyperlinks on sheet 2 or stored as text in cells , it makes a huge difference to the code you need depending on the answer. Also how are you going to identify which hyperlink you want i.e which column on sheet 1 matches which column on sheet2 , Which column on sheet 2 are the hyperlinks in??

 

[dagda13]

Hi offthelip,

Sorry, my bad that I was unclear. The below is more accurate code:

Sub GetHyperlink()
     For Each xCell In Range("A:A")
          If xCell.Value <> "" Then
               ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:="IFNA(VLOOKUP(E" & ActiveCell.Row & ",Hidden!A:O,15,0),0)", TextToDisplay:=xCell.Value
          End If
     Next xCell
End Sub

The "Hidden" tab contains a column of ID's (column A) and corresponding URLs (column O).

Column E in "Sheet1" is a Vlookup against column A in "Hidden".