Convert Xlookup to direct link for selected Range.
-
- 4StarLounger
- Posts: 432
- Joined: 23 Mar 2017, 19:51
Convert Xlookup to direct link for selected Range.
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.
-
- 4StarLounger
- Posts: 432
- Joined: 23 Mar 2017, 19:51
Re: Convert Xlookup to direct link for selected Range.
Macro workbook with Xlookup VBA.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78577
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Convert Xlookup to direct link for selected Range.
Your workbook doesn't contain any XLOOKUP formulas, so it's not clear what you want.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 432
- Joined: 23 Mar 2017, 19:51
Re: Convert Xlookup to direct link for selected Range.
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.
-
- Administrator
- Posts: 78577
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Convert Xlookup to direct link for selected Range.
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
Hans
-
- 4StarLounger
- Posts: 432
- Joined: 23 Mar 2017, 19:51
Re: Convert Xlookup to direct link for selected Range.
Yes, it works but for active cell only, I need for the selected range. How can we change code to execute this?
-
- Administrator
- Posts: 78577
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Convert Xlookup to direct link for selected Range.
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
Hans
-
- 4StarLounger
- Posts: 432
- Joined: 23 Mar 2017, 19:51
Re: Convert Xlookup to direct link for selected Range.
O yees, It works great, huge Thanks.
-
- 4StarLounger
- Posts: 432
- Joined: 23 Mar 2017, 19:51
Re: Convert Xlookup to direct link for selected Range.
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?
-
- 4StarLounger
- Posts: 432
- Joined: 23 Mar 2017, 19:51
Re: Convert Xlookup to direct link for selected Range.
The XLOOKUP-Go-To-Source-Macro" book extension is (.xlsm).
-
- Administrator
- Posts: 78577
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Convert Xlookup to direct link for selected Range.
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)
- 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
Hans
-
- 4StarLounger
- Posts: 432
- Joined: 23 Mar 2017, 19:51
Re: Convert Xlookup to direct link for selected Range.
Thanks but how convert .xlsm to xlsb file?
And why it would be better to store file in xlsb file?
And why it would be better to store file in xlsb file?
-
- Administrator
- Posts: 78577
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Convert Xlookup to direct link for selected Range.
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
Copy your macros to a Personal Macro Workbook
Excel Personal Macro Workbook | Save & Use Macros in All Workbooks
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 432
- Joined: 23 Mar 2017, 19:51
Re: Convert Xlookup to direct link for selected Range.
Thanks, I undesrtand now.