Sleeper: Help with VBA code and XLOOKUP

[cgreen16]

Hello,

I'm very new to VBA coding and I'm struggling with getting my VBA code to perform an XLOOKUP. I have 2 sheets in my workbook named "Election Summary" and "Autorebal". I used variables to store each argument of the XLOOKUP function. I'm honestly not sure if this is the best way to do this. I think since the variables I created for the arguments (lookup_value, lookup_array) are on different worksheets, it's causing issues. The reason i say this is because if I'm currently on the 'Election Summary' worksheet and just run a lookup_array.SELECT, it gives me a 'Select method of range clase failed' message. The lookup_array variable is on the 'Autorebal' worksheet.

The argument ranges of the XLOOKUP function will vary, so I will have to find the last cell on those ranges. Lastly, I don't think the autofill part is correct, but this was my best attempt to work through that. I'll try to attach the spreadsheet I'm working with and there's a tab on there that shows what I'd like my end goal to look like. Below is the coding i have so far, and I'm stuck on where to go next. Any help is greatly appreciated!



Dim es As Worksheet
Dim ar As Worksheet
Dim lookup_value As Range
Dim lookup_array As Range
Dim return_array As Range
Dim if_not_found As String
Set es = Worksheets("Election Summary")
Set ar = Worksheets("Autorebal")
Set lookup_value = es.Range("$A$5:$A$" & Cells.SpecialCells(xlCellTypeLastCell).Row)
Set lookup_array = ar.Range("$A$2:$A$" & Cells.SpecialCells(xlCellTypeLastCell).Offset(-1).Row)
Set return_array = Range("B2:C" & Cells.SpecialCells(xlCellTypeLastCell).Offset(-1).Row)
if_not_found = ""
Sheets("Election Summary").Select
Range("D5").Select
Range("D5").Value = Application.WorksheetFunction.XLookup(lookup_value, lookup_array, return_array, if_not_found)
Selection.AutoFill Destination:=Range("D5:E" & Cells.SpecialCells(xlCellTypeLastCell).Row)

[p45cal]

2 things wrong:

1. XLOOKUP doesn't return an array of arrays (it should, but MS hasn't done it yet!).
2. You have unqualified references in the code, eg.:
Set lookup_value = es.Range("$A$5:$A$" & Cells.SpecialCells(xlCellTypeLastCell).Row)
should be:
Set lookup_value = es.Range("$A$5:$A$" & es.Cells.SpecialCells(xlCellTypeLastCell).Row)

and the likes of:
Set return_array = Range("B2:C" & Cells.SpecialCells(xlCellTypeLastCell).Offset(-1).Row)
should be:
Set return_array = ar.Range("B2:C" & ar.Cells.SpecialCells(xlCellTypeLastCell).Offset(-1).Row)
although because of point 1 above I've split this into 2 lines.

Try:

Sub Autorebal_move()

Dim es As Worksheet, ar As Worksheet
Dim lookup_value As Range, lookup_array As Range, return_array1 As Range, return_array2 As Range, Destn As Range
Dim if_not_found As String

Set es = Worksheets("Election Summary")
Set ar = Worksheets("Autorebal")
Set lookup_value = es.Range("$A$5:$A$" & es.Cells.SpecialCells(xlCellTypeLastCell).Row)
Set lookup_array = ar.Range("$A$2:$A$" & ar.Cells.SpecialCells(xlCellTypeLastCell).Offset(-1).Row)
Set return_array1 = ar.Range("B2:B" & ar.Cells.SpecialCells(xlCellTypeLastCell).Offset(-1).Row)
Set return_array2 = ar.Range("C2:C" & ar.Cells.SpecialCells(xlCellTypeLastCell).Offset(-1).Row)
if_not_found = ""

Set Destn = es.Range("D5")
Destn.Resize(lookup_value.Rows.Count) = Application.XLookup(lookup_value, lookup_array, return_array1, if_not_found)
Destn.Offset(, 1).Resize(lookup_value.Rows.Count) = Application.XLookup(lookup_value, lookup_array, return_array2, if_not_found)
End Sub


An easier way of setting the return arrays is to use an offset of lookup_array:
Set lookup_array = ar.Range("$A$2:$A$" & ar.Cells.SpecialCells(xlCellTypeLastCell).Offset(-1).Row)
Set return_array1 = lookup_array.Offset(, 1)
Set return_array2 = lookup_array.Offset(, 2)


Or don't bother setting them (or the worksheets) at all:
Sub Autorebal_move()
Dim lookup_value As Range, lookup_array As Range, Destn As Range
Dim if_not_found As String

With Worksheets("Election Summary")
Set lookup_value = .Range("$A$5:$A$" & .Cells.SpecialCells(xlCellTypeLastCell).Row)
Set Destn = .Range("D5")
End With

With Worksheets("Autorebal")
Set lookup_array = .Range("$A$2:$A$" & .Cells.SpecialCells(xlCellTypeLastCell).Offset(-1).Row)
End With

if_not_found = ""

Destn.Resize(lookup_value.Rows.Count) = Application.XLookup(lookup_value, lookup_array, lookup_array.Offset(, 1), if_not_found)
Destn.Offset(, 1).Resize(lookup_value.Rows.Count) = Application.XLookup(lookup_value, lookup_array, lookup_array.Offset(, 2), if_not_found)
End Sub

…and another way to set the lookup_array (which caters for different sizes of pivot table):

Set lookup_array = Worksheets("Autorebal").PivotTables(1).PivotFields("Ticker").DataRange with no need for the With…End With construct around that line.

[hazael4]

Hello,

I'm very new to VBA coding and I'm struggling with getting my VBA code to perform an XLOOKUP. I have 2 sheets in my workbook named "Election Summary" and "Autorebal". I used variables to store each argument of the XLOOKUP function. I'm honestly not sure if this is the best way to do this. I think since the variables I created for the arguments (lookup_value, lookup_array) are on different worksheets, it's causing issues. The reason i say this is because if I'm currently on the 'Election Summary' worksheet and just run a lookup_array.SELECT, it gives me a 'Select method of range clase failed' message. The lookup_array variable is on the 'Autorebal' worksheet.

The argument ranges of the XLOOKUP function will vary, so I will have to find the last cell on those ranges. Lastly, I don't think the autofill part is correct, but this was my best attempt to work through that. I'll try to attach the spreadsheet I'm working with and there's a tab on there that shows what I'd like my end goal to look like. Below is the coding i have so far, and I'm stuck on where to go next. Any help is greatly appreciated!



Dim es As Worksheet
Dim ar As Worksheet
Dim lookup_value As Range
Dim lookup_array As Range
Dim return_array As Range
Dim if_not_found As String
Set es = Worksheets("Election Summary")
Set ar = Worksheets("Autorebal")
Set lookup_value = es.Range("$A$5:$A$" & Cells.SpecialCells(xlCellTypeLastCell).Row)
Set lookup_array = ar.Range("$A$2:$A$" & Cells.SpecialCells(xlCellTypeLastCell).Offset(-1).Row)
Set return_array = Range("B2:C" & Cells.SpecialCells(xlCellTypeLastCell).Offset(-1).Row)
if_not_found = ""
Sheets("Election Summary").Select
Range("D5").Select
Range("D5").Value = Application.WorksheetFunction.XLookup(lookup_value, lookup_array, return_array, if_not_found)
Selection.AutoFill Destination:=Range("D5:E" & Cells.SpecialCells(xlCellTypeLastCell).Row)
Regarding the "Select method of range class failed" error, it's often best to avoid using Select in VBA. Instead, you can directly reference the ranges without selecting them.