VBA Xlookup on second sheet.

[JimS63]

I have a second sheet which contains a table with the following columns.

Slicing request Slicing request hyperlink Hyperlink address
This is =VALUETOTEXT(C3) This is copy and paste from another worksheet This is =GetURL(C3)

Ultimately I would like to enter a value in a form on the first sheet for the slicing request number. It is in the format of ASR-#####
I would like the hyperlink that is found in the second column of the table on the second sheet to be pasted into the active cell of the current active sheet (which is the first sheet)

I have the following code in my Form

Dim Request As String
Dim FullASR As String



Request = UserForm1.TextBox5
FullASR = "ASR-" & Format(Request, "00000")

' LinkASR = [XLOOKUP(FullASR,Table1[Slicing Request],Table1[Clickable Link])),FullASR)]

ActiveCell.Value = [HYPERLINK(XLOOKUP(FullASR,'Table1[Slicing Request]','Table1[Link Contents]'),FullASR)]

It is returning the following error
#VALUE!

Is there a way to do this?

Thanks for your help.

 

[Domenic]

Try . . .

    Dim retVal As Variant
    retVal = Application.XLookup(FullASR, Range("Table1[Slicing Request]"), Range("Table1[Clickable Link]"))
    
    If Not IsError(retVal) Then
        ActiveCell.Hyperlinks.Add ActiveCell, retVal, , , retVal
    Else
        ActiveCell.Value = FullASR
    End If

Hope this helps!

 

[JimS63]

Dim retVal As Variant retVal = Application.XLookup(FullASR, Range("Table1[Slicing Request]"), Range("Table1[Clickable Link]")) If Not IsError(retVal) Then ActiveCell.Hyperlinks.Add ActiveCell, retVal, , , retVal Else ActiveCell.Value = FullASR End If

This gives me the following link. No quite sure I understand but it appears that it knows if the lookup value is available and if not it just gives me the value of FullASR but the link is incorrect.
I think this is close but I can't quite get it. Does the fact that the Table1 resides on a different sheet make a difference? The table resides on a sheet named SR_Link

 

[Domenic]

Oh sorry, my mistake, the return range for XLookup should have been Link Contents, not Clickable Link . . .

retVal = Application.XLookup(FullASR, Range("Table1[Slicing Request]"), Range("Table1[Link Contents]"))

 

[JimS63]

That works perfectly!

Last question can I concat this expression on the end of this one?

 ActiveCell.Value = CName & Chr(10) & PName & Chr(10) & DTime & "   Shelf " & strLNameUCase & Chr(10) & "Request "

Thanks again

 

[Domenic]

Do you mean that you want to add it to the end of the "TextToDisplay" string for your link? If not, can you please clarify?

 

[JimS63]

Do you mean that you want to add it to the end of the "TextToDisplay" string for your link? If not, can you please clarify?

I would like the cell to look like this when done. The first three lines are inputs from the form. Or if I could add a graphic that links to the website that works as well.

 

[Domenic]

Is this what you mean?

ActiveCell.Hyperlinks.Add ActiveCell, retVal, , , CName & Chr(10) & PName & Chr(10) & DTime & "   Shelf " & strLNameUCase & Chr(10) & "Request " & FullASR

 

[JimS63]

Is this what you mean?

ActiveCell.Hyperlinks.Add ActiveCell, retVal, , , CName & Chr(10) & PName & Chr(10) & DTime & "   Shelf " & strLNameUCase & Chr(10) & "Request " & FullASR

Perfect!!! I was trying to put the hyperlink statement inside the concat statement instead of putting the concat statement inside the hyperlink. Thank you so much

 

[Domenic]

You're very welcome, cheers!