VBA VLOOKUP formula to refer to a dynamic range in another sheet

Question

VBA VLOOKUP formula to refer to a dynamic range in another sheet

Hi, I am trying to incorporate a VLOOKUP formula into a worksheet called "RESULTS" and reference a dynamic data range in another sheet called "DATA" via VBA - the formula I have works fine but it is not dynamic and I have not been unable to replicate it in the macro, as such, given my limited VBA skills, I have hit a bit of a brick wall so any adice and guidance would be greatly appreciated.

This is the formula I want to replicate for use in the macro but, as mentioned, it is a fixed range and I need it to be dynamic:

=IF(IFERROR(VLOOKUP(A2,IF({1,0},DATA!$C$5:$C$20000,DATA!$B$5:$B$20000),2,0),"")=0,"",IFERROR(VLOOKUP(A2,IF({1,0},DATA!$C$5:$C$20000,DATA!$B$5:$B$20000),2,0),""))

Here is the VBA I have written - it is just the VLOOKUP formula that isn't working, the COUNTIFS formula is working fine.

Sub DATA_Report()
        
    'Add in formulae
    Dim DataLastRow As Long
    Dim ResultsLastRow As Long

    'Determine last row of DATA Scheme Information sheet
     With Sheets("DATA")
        DataLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    'Determine last row in col B for RESULTS sheet - Add in formula - MEMBER REFERENCE
    With Sheets("RESULTS")
    ResultsLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        'The following formula is not working
        .Range("G2:G" & .Cells(.Rows.Count, "A").End(xlUp).Row).FormulaR1C1 = "=IF(IFERROR(VLOOKUP(RC[-6],IF({1,0}," & "DATA!R5C3:R" & DataLastRow & ",DATA!R5C2:R" & DataLastRow & "),2,0)," & """"")=0," & """""," & "IFERROR(VLOOKUP(RC[-6],IF({1,0}," & "DATA!R5C3:R" & DataLastRow & ",DATA!R5C2:R" & DataLastRow & "),2,0),""""))"
        'The following COUNTIFS formula works fine
        .Range("H2:AA" & ResultsLastRow).FormulaR1C1 = "=IF(COUNTIFS(DATA!R5C2:R" & DataLastRow & "C2,RC7,DATA!R5C1:R" & DataLastRow & "C1,R1C)>0,""Yes"","""")"
    End With
End Sub

Many thanks in advance, TE