Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA Help - Stuck on XLOOKUP and assigning variables

0

Hello,

I'm very new to VBA and struggling with the best way to incorporate XLOOKUP in my VBA code. To me the tricky part is getting my variables to be dynamic based on the row count in my data.

I have 2 worksheets called 'Election Summary' and 'Autorebal.'  The XLOOKUP function's argument lookup-value is a range from 'Election Summary' workseet.The lookup_array argument is a Range in the 'Autorebal' worksheet. The return_array is also a range in the 'Autorebal' worksheet

My understanding is that since the ranges need to be dynamic and grab the last row,  that creating variables for the lookup_valuelookup_array, and return_array is my best bet. 

Here is my code so far: 

    Dim vRows As Integer
    Dim aRows As Integer
    Dim rRows As Integer

    vRows = Range(Range("A5"), Range("A5").End(xlDown)).Rows.Count + 4
    aRows = Range(Range("A2"), Range("A5").End(xlDown)).Rows.Count - 1
    Sheets("Election Summary").Select
    Range("D5").Select
    ActiveCell.Formula2R1C1 = _
        "=XLOOKUP(R5C1:R" & vRows & "C1,Autorebal!R2C1:R27C1,Autorebal!R[-3]C[-2]:R[22]C[-1],"""")"
    Range("D5").Select
    Selection.AutoFill Destination:=Range("D5:E5"), Type:=xlFillDefault 

vRows would be a Range in the 'Election Summary' worksheet

aRows would be a Range in the 'Autorebal' workseet

rRows would be a Range in the 'Autorebal' worksheet

In the Xlookup Formula above, I have inserted the vRows variable in the string where the lookup_value argument is and that seems to work ok. 

The problem is inserting a variable in the lookup_array argument that works because that range is on on different worksheet (Autorebal). I haven't been able to create a variable that is dynamic that recognizes the range is on Autorebal worksheet.

My main question is how to create a dynamice variable for the lookup_array and return array where both of these arguments are on a different worksheet (both on the 'Autorebal' worksheet)?

I've attached the spreadsheet with the code that I'm currently working on. 

Any help is greatly appreciated!

Answer
Discuss

Answers

0
Selected Answer

Hi Cayton and welcome to the Forum

In the attached revised file, I've added a new VBA module (Module 3).

The first macro in that will add the values from worksheet Autorebal to your summary sheet. I haven't made them cells contain a formula but used XLOOKUP to match the codes (like "PTTRX") like you did. The comments should help you see what's happening (and bold bits to show subtle changes): 

Sub UpSumm()

    Dim eRows As Integer, aRows As Integer, ThisRow As Integer
    Dim LkRng As Range

    ' determine last data row in Autorebal
    aRows = Worksheets("Autorebal").Range("A" & Rows.Count).End(xlUp).Row - 1
    ' fix range column to look in
    Set LkRng = Worksheets("Autorebal").Range("A2:A" & aRows)

    Worksheets("Election Summary").Activate
    ' determine last data row in Summary
    eRows = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

    ' loop down column A from row 5
    For ThisRow = 5 To eRows
        ' match A and get the value for column D (4)
        Cells(ThisRow, 4).Value = Application.WorksheetFunction.xlookup _
            (Cells(ThisRow, 1).Value, LkRng, LkRng.Offset(0, 1), "-", 0)
        '  then get the value for next column E (5) from next column
        Cells(ThisRow, 5).Value = Application.WorksheetFunction.xlookup _
            (Cells(ThisRow, 1).Value, LkRng, LkRng.Offset(0, 2), "-", 0)
    Next ThisRow

End Sub

If columns D and E and clear in the summary, run the code and the values will be populated. If you want cells with no values to appear blank, just change the bits "-" above to "".

I suspect you have other "balances" to do later so the second procedure in Module 3 is similar but looks for the last column heading (in row 3 of Summary) and extracts values from the last column of Autorebal to put in the adjacent column. Here's the code, see bits in bold:

Sub AddLastColToSumm()

    Dim eRows As Integer, aRows As Integer, ThisRow As Integer
    Dim LkRng As Range
    Dim eCol As Integer, aCol As Integer

    ' determine last data row in Autorebal
    aRows = Worksheets("Autorebal").Range("A" & Rows.Count).End(xlUp).Row - 1
    ' get last column in row 1
    aCol = Worksheets("Autorebal").Cells(1, Columns.Count).End(xlToLeft).Column
    ' fix range column to look in
    Set LkRng = Worksheets("Autorebal").Range("A2:A" & aRows)

    Worksheets("Election Summary").Activate
    ' determine last data row in Summary
    eRows = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    ' determine last data column in row 3
    eCol = ActiveSheet.Cells(3, Columns.Count).End(xlToLeft).Column
    Cells(3, eCol + 1).Value = "Update " & Format(Now, "hh:mm mmm dd yyyy")

    ' loop down column A from row 5
    For ThisRow = 5 To eRows
        ' match A and get the value from last column
        Cells(ThisRow, eCol + 1).Value = Application.WorksheetFunction.xlookup _
            (Cells(ThisRow, 1).Value, LkRng, LkRng.Offset(0, aCol - 1), "-", 0)
    Next ThisRow

End Sub

This will put some timed text in row 3 and the values from the last (presumably new) column in Autorebal.

Take a look in the Tutorials section to understand how the last columns were deteremined but don't forget to come back here...

If this helps, please remember to mark this Answer as Selected. Thanks in advance.

Discuss

Discussion

Thank you! This is exactly what i was looking for. Much appreciated!
cgreen16 (rep: 6) Nov 13, '23 at 5:45 pm
Glad that worked for you Cayton. Thanks for selecting my Answer. 
John_Ru (rep: 6297) Nov 13, '23 at 6:18 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login