XLOOKUP Built by VBA InputBox

[eMTB]

I am trying to make a macro that will create an XLOOKUP for me. This is my first post so if I do something wrong let me know. This is my code and it's not working. Can anyone see why?


Sub XLOOKUP()

Dim NUM1 As Variant
Dim NUM2 As Variant
Dim NUM3 As Variant

NUM1 = Application.InputBox("Enter the value you want to lookup.", "Lookup Value", Type:=1)
NUM2 = Application.InputBox("Enter the range you want to find the lookup value.", "Lookup Array ", Type:=1)
NUM3 = Application.InputBox("Enter the range that you want to return.", "Return Array", Type:=1)

ActiveCell.Formula = "=XLOOKUP(NUM1 & CHAR(34) & NUM2 & CHAR(34) & NUM3)"

End Sub

 

[Domenic]

For your lookup value, you're using Type 1, so I'm assuming you want the user to inpurt a number.

For your lookup array and return array, I'm assuming that you want the user to select a range of cells for each of them. If so, it should be Type 8.

Accordingly, try the following code . . .

Sub XLOOKUP()

    Dim lookupValue As Variant
    Dim lookupArray As Range
    Dim returnArray As Range
    
    lookupValue = Application.InputBox("Enter the value you want to lookup.", "Lookup Value", Type:=1)
    If lookupValue = False Then Exit Sub
    
    On Error Resume Next
    Set lookupArray = Application.InputBox("Enter the range you want to find the lookup value.", "Lookup Array ", Type:=8)
    If Err <> 0 Then Exit Sub
    Set returnArray = Application.InputBox("Enter the range that you want to return.", "Return Array", Type:=8)
    If Err <> 0 Then Exit Sub
    On Error GoTo 0
      
    ActiveCell.Formula = "=XLOOKUP(" & lookupValue & "," & lookupArray.Address(, , , True) & "," & returnArray.Address(, , , True) & ")"

End Sub

Hope this helps!

 

[Peter_SSs]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: XLOOKUP Built by VBA InputBox
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Alex Blakenburg]

Is that really worth the trouble ? You could just do this:

Sub myXlookup()

    ActiveCell.Formula = "=Xlookup(,,)"
    Application.Dialogs(xlDialogFunctionWizard).Show

End Sub

 

[eMTB]

Is that really worth the trouble ? You could just do this:

Sub myXlookup()

    ActiveCell.Formula = "=Xlookup(,,)"
    Application.Dialogs(xlDialogFunctionWizard).Show

End Sub

I more wanted to see if it could be done to potentially open up possibilities elsewhere. But your method works really well too. I never thought about pulling up the function wizard.

 

[Alex Blakenburg]

Thanks for the feedback. You might want to let Domenic know if his code works for you.

 

[eMTB]

For your lookup value, you're using Type 1, so I'm assuming you want the user to inpurt a number.

For your lookup array and return array, I'm assuming that you want the user to select a range of cells for each of them. If so, it should be Type 8.

Accordingly, try the following code . . .

Sub XLOOKUP()

    Dim lookupValue As Variant
    Dim lookupArray As Range
    Dim returnArray As Range
   
    lookupValue = Application.InputBox("Enter the value you want to lookup.", "Lookup Value", Type:=1)
    If lookupValue = False Then Exit Sub
   
    On Error Resume Next
    Set lookupArray = Application.InputBox("Enter the range you want to find the lookup value.", "Lookup Array ", Type:=8)
    If Err <> 0 Then Exit Sub
    Set returnArray = Application.InputBox("Enter the range that you want to return.", "Return Array", Type:=8)
    If Err <> 0 Then Exit Sub
    On Error GoTo 0
     
    ActiveCell.Formula = "=XLOOKUP(" & lookupValue & "," & lookupArray.Address(, , , True) & "," & returnArray.Address(, , , True) & ")"

End Sub

Hope this helps!

Thanks for the response! That is exactly what I was trying to do.

 

[eMTB]

Thanks for the response! That is exactly what I was trying to do

For your lookup value, you're using Type 1, so I'm assuming you want the user to inpurt a number.

For your lookup array and return array, I'm assuming that you want the user to select a range of cells for each of them. If so, it should be Type 8.

Accordingly, try the following code . . .

Sub XLOOKUP()

    Dim lookupValue As Variant
    Dim lookupArray As Range
    Dim returnArray As Range
   
    lookupValue = Application.InputBox("Enter the value you want to lookup.", "Lookup Value", Type:=1)
    If lookupValue = False Then Exit Sub
   
    On Error Resume Next
    Set lookupArray = Application.InputBox("Enter the range you want to find the lookup value.", "Lookup Array ", Type:=8)
    If Err <> 0 Then Exit Sub
    Set returnArray = Application.InputBox("Enter the range that you want to return.", "Return Array", Type:=8)
    If Err <> 0 Then Exit Sub
    On Error GoTo 0
     
    ActiveCell.Formula = "=XLOOKUP(" & lookupValue & "," & lookupArray.Address(, , , True) & "," & returnArray.Address(, , , True) & ")"

End Sub

Hope this helps!

How would I do it without the absolute cell reference?