excel vba loop through values in a column and get hyperlinks from another sheet using Vloo

Hi,

I'd like to use excel vba to loop through values in a column and get hyperlinks from another sheet using Vlookup and use the original values in the column as the friendly name. Is this possible? I've tried the below code but only get Run-time error '1004': Unable to get the Vlookup property of the WorksheetFunction class. Is there anyway to get this to work?

Sub Get_Links_From_Hidden_Tab_Using_VLOOKUP_and_Give_It_The_Original_ID_As_Friendly_Name()


For Each xCell In Range("A:A")

Dim x As Variant
x = xCell.Value

If xCell.Value <> "" Then

ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=Application.WorksheetFunction.VLookup(x, Worksheets("Hidden").Range("A:O"), 15, 0), TextToDisplay:=xCell.Value

End If

Next xCell

End Sub

Maybe try:

Please Login or Register  to view this content.

Hi CheeseSandwich,

Thanks very much! It works perfectly except for one issue. If there happens to be a blank/empty cell in the list that has no value (and this does happen with some of our lists that we're trying to add links to), the macro throws a Run-time error '13': Type mismatch.

I'm wondering if the variable type for addr should changed from String to another type, maybe? Or do you know if this is another issue entirely? Just some way to handle blank cells and / or cells that have no match in the "Hidden" sheet?

Maybe:

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

Hi CheeseSandwich,

This works perfectly. Thanks so much!

Hi CheeseSandwich,

Sorry, I actually spoke too soon. I still get the mismatch error when there is a value in the cell list in the first sheet that has no match in the Hidden sheet...(the empty value issue has been fixed).

Hmm ok try:

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

It is counting to see if the value it is looking up exists in the lookup sheet

These both work perfectly. Can't thank you enough for all the time you've put in for helping me out with this. Very much appreciated!

Yep, these are perfect! Thanks so much, again! Wish I could Add Reputation more than just once.

You are very welcome

I enjoy problem solving, it is the pickle in my sandwich...

Are you able to format the lookup column A in the hidden sheet as text?

Yes, I've formatted both columns (column A in Sheet1 and Column A in "Hidden") as text (with the cstr change to the code) but still the numeric-only IDs do not have a links generated.

Ahh I placed the Cstr part in the wrong place (I was trying to help from my mobile) it should have been on the TextToDisplay part of that line. I have also added a check so that it does not assign a hyperlink if it does not find the value in the Hidden tab.

Please Login or Register  to view this content.

Success! Works absolutely perfectly. Tried to give you more reputation but it won't let me. Earned it and then some. Thanks so much, again.