Convert Xlookup to direct link for selected Range.

vaxo
4StarLounger
Posts: 432
Joined: 23 Mar 2017, 19:51

Convert Xlookup to direct link for selected Range.

Post by vaxo »

Hello Friends, In my VBA application there is assigned macro which converts vlookup to a direct link ( so that, I can use "CTRL-[" and go to direct cell) and now I need a script which will do the same for xlookup. Can someone help accomplishing this?
You do not have the required permissions to view the files attached to this post.

vaxo
4StarLounger
Posts: 432
Joined: 23 Mar 2017, 19:51

Re: Convert Xlookup to direct link for selected Range.

Post by vaxo »

Macro workbook with Xlookup VBA.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Convert Xlookup to direct link for selected Range.

Post by HansV »

Your workbook doesn't contain any XLOOKUP formulas, so it's not clear what you want.
Best wishes,
Hans

vaxo
4StarLounger
Posts: 432
Joined: 23 Mar 2017, 19:51

Re: Convert Xlookup to direct link for selected Range.

Post by vaxo »

In this file, I have added xlookup formula and embedded macro works, but it does not change xlookup link like in the picture - ( first picture).
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Convert Xlookup to direct link for selected Range.

Post by HansV »

Does this do what you want?

Code: Select all

Sub XLOOKUP_Go_To_Source_Cell()

    Dim rResult As Range
    
    'XLOOKUP returns a range
    On Error Resume Next
        Set rResult = Evaluate(ActiveCell.Formula)
    On Error GoTo 0
    
    'Modify formula
    If Not rResult Is Nothing Then
        ActiveCell.Formula = "=" & rResult.Address(External:=True)
    Else
        'Display message box if activecell does not contain an XLOOKUP
        MsgBox "Please select a cell that contains an XLOOKUP formula.", vbOKOnly, "XLOOKUP Go To Source"
    End If

End Sub
Best wishes,
Hans

vaxo
4StarLounger
Posts: 432
Joined: 23 Mar 2017, 19:51

Re: Convert Xlookup to direct link for selected Range.

Post by vaxo »

Yes, it works but for active cell only, I need for the selected range. How can we change code to execute this?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Convert Xlookup to direct link for selected Range.

Post by HansV »

Code: Select all

Sub XLOOKUP_Go_To_Source_Cell()
    Dim rCell As Range
    Dim rResult As Range
    
    For Each rCell In Selection
        Set rResult = Nothing
        On Error Resume Next
        Set rResult = Evaluate(rCell.Formula)
        On Error GoTo 0
    
        'Modify formula
        If Not rResult Is Nothing Then
            rCell.Formula = "=" & rResult.Address(External:=True)
        End If
    Next rCell
End Sub
Best wishes,
Hans

vaxo
4StarLounger
Posts: 432
Joined: 23 Mar 2017, 19:51

Re: Convert Xlookup to direct link for selected Range.

Post by vaxo »

O yees, It works great, huge Thanks.

vaxo
4StarLounger
Posts: 432
Joined: 23 Mar 2017, 19:51

Re: Convert Xlookup to direct link for selected Range.

Post by vaxo »

One question: I have saved "XLOOKUP-Go-To-Source-Macro" workbook to XLSTART Folder and added to quick Access Toolbar and it workes great, but every time I open a new excel file, the file name is "XLOOKUP-Go-To-Source-Macro". What mistake did I make?

vaxo
4StarLounger
Posts: 432
Joined: 23 Mar 2017, 19:51

Re: Convert Xlookup to direct link for selected Range.

Post by vaxo »

The XLOOKUP-Go-To-Source-Macro" book extension is (.xlsm).

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Convert Xlookup to direct link for selected Range.

Post by HansV »

Try this:
- Open the workbook.
- On the View tab of the ribbon, in the Window group, click Hide.
- Quit Excel. It will ask whether you want to save changes to the workbook.Answer Yes.

It would be better to store the macro in your Personal Macro Workbook PERSONAL.XLSB (also in the XLSTART folder)
Best wishes,
Hans

vaxo
4StarLounger
Posts: 432
Joined: 23 Mar 2017, 19:51

Re: Convert Xlookup to direct link for selected Range.

Post by vaxo »

Thanks but how convert .xlsm to xlsb file?
And why it would be better to store file in xlsb file?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Convert Xlookup to direct link for selected Range.

Post by HansV »

PERSONAL.XLSB is a special kind of workbook, that name and extension is obligatory, you cannot change it. See
Copy your macros to a Personal Macro Workbook
Excel Personal Macro Workbook | Save & Use Macros in All Workbooks
Best wishes,
Hans

vaxo
4StarLounger
Posts: 432
Joined: 23 Mar 2017, 19:51

Re: Convert Xlookup to direct link for selected Range.

Post by vaxo »

Thanks, I undesrtand now.