VLookup from Last Column in Range VBA-CodePudding

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)"

CodePudding user response：

Try something like this:

Sub VLookupLastColumn()

    Dim Row7 As Long, Column1 As Long
    Dim myTableArray As Range, ws As Worksheet
    
    Set ws = Sheets("Pivot Values") 'use a worksheet variable
    
    Row7 = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Column1 = ws.Cells(1, Columns.Count).End(xlToLeft).Column
    Set myTableArray = ws.Range(ws.Cells(1, 1), ws.Cells(Row7, Column1))
    
    'add countif starting at row 7
    ws.Range(ws.Cells(7, Column1   1), ws.Cells(Row7, Column1   1)).Formula = _
         "=COUNTIF(B7:" & ws.Cells(7, Column1).Address(False, False) & ","">0"")"
    
    With Sheets("Output").Range("BD5")
        .Formula = "=VLOOKUP(" & .Offset(0, -55).Address(False, False) & "," & _
                             ExtAddress(myTableArray) & "," & Column1 & ",FALSE)"
    End With

End Sub

'return a range address, including the worksheet name
Function ExtAddress(rng As Range)
    ExtAddress = "'" & rng.Parent.Name & "'!" & rng.Address()
End Function