cgreen16
11-07-2023, 10:17 PM
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)
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)