VLookup from Last Column in Range VBA

0
votes

I'm attempting to write VBA that will VLookup information from an array on a different worksheet and return it. The issue is, my code needs to lookup the information from the last column in the range, however since the range is a pivot table and the columns are months with backlog, the number of columns may differ. The VBA is successful at putting the code into the target cell but it gets a #NAME? error so there's something getting messed up with the object declarations I believe. Can someone please assist?

Sub VLookupLastColumn()

Dim Row7 As Long
Dim Column1 As Long
Dim myTableArray As Range


Sheets("Pivot Values").Select
Row7 = Range("A" & Rows.Count).End(xlUp).Row
Column1 = Range("A" & Columns.Count).End(xlToLeft).Column
With ActiveSheet
    Set myTableArray = .Range(.Cells(1, 1), .Cells(Row7, Column1))
End With

Sheets("Output").Select
Range("BD5").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-55],myTableArray,Column1,0)"
excelvbavlookup