How to include dynamic VLOOKUP in FormulaR1C1 VBA?

March 30, 2023

I need to lookup values on another sheet. My formula works great, when i use it manually

=VLOOKUP(TRIM(C2),TRIM('MyDataSheet'!$A$1:$E$500),4,FALSE)

However, I need to be able to plug this formula into a cell dynamically using VBA. This is what I tried:

 Set lookupRange = ThisWorkbook.Sheets("MyDataSheet").Range("A1:E500")
 Set newCol = tbl.ListColumns.Add
 newCol.DataBodyRange.FormulaR1C1 = "=VLOOKUP(TRIM(RC[-16])," & lookupRange.Address(True, True, xlR1C1) & ", 4, FALSE)"

This plugs in formula in Excel like this, BUT completely ignores the different sheet and ignores the second TRIM:

=VLOOKUP(TRIM(C2),$A$1:$E$500),4,FALSE)

How to write VBA to dynamically insert VLOOKUP as this: =VLOOKUP(TRIM(C2),TRIM('MyDataSheet'!$A$1:$E$500),4,FALSE)

>Solution :

You could do it like this:

Sub Tester()
    
    Dim tbl As ListObject, newCol As ListColumn, c As Range
    
    Set tbl = ActiveSheet.ListObjects(1) 'for example
    Set newCol = tbl.ListColumns.Add
    newCol.Name = "Lookup"

    ' "id" is the column with the values you want to look up
    Set c = tbl.ListColumns("id").DataBodyRange.Cells(1) 
    
    newCol.DataBodyRange.Formula2 = _
         "=VLOOKUP(TRIM(" & c.Address(False, False) & "),TRIM('MyDataSheet'!$A$1:$E$500),4,FALSE)"
    
End Sub