Nested IFERROR, VLOOKUP, IF VBA code + copy down

user_12274846 0
Viewed 223

I am trying to create a VBA code to do this formula: =IFERROR(Vlookup(C3,PasteValues!$A:$F,6,0),IF(Q2="","",Q2)) I also want to copy this formula down into the entire column. 

Dim LastRow As Long
Range("P:P").Copy Range("Q:Q")
LastRow = Range("P2:P" & Rows.Count).End(xlUp).Row
Range("P2:P" & LastRow).Formula = Application.WorksheetFunction.IFERROR(VLOOKUP(Range("C2"),Range("TableEmail!A:F"),6,FALSE),IF(Q2="","",C2))

When I tried this, the formula did not copy down/returned syntax error.

vba excel
1 Answers

If you want to fill the formula it must be a string. One only uses Application.WorksheetFunction when wanting to use that function to return a value in vba. If what is wanted is to place the formula in a cell, or range, using .Formula then the formula is a string in US-EN format with any " inside the string doubled:

With ActiveSheet
    Dim LastRow As Long
    .Range("P:P").Copy .Range("Q:Q")
    LastRow = .Range("P" & Rows.Count).End(xlUp).Row
    .Range("P2:P" & LastRow).Formula = "=IFERROR(VLOOKUP(C2,TableEmail!A:F,6,FALSE),IF(Q2="""","""",C2))"
End With
user_48515900
Related