VBA Help - Stuck on XLOOKUP and assigning variables

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.