VBA - Adding Formulas (IFERROR not working)

[Jimmers]

Hi all,

I've got an SAP Script which I am running and once all the extracts have been done, I've added some VBA to create Formulas (VLOOKUPS) in cells and extend to the end of the last available row.

However when executing they keep failing as I am trying to use IFERROR the code fails, I'm a novice as I've said before.

Heres the portion of code;

    Dim Lastrow As Long
    
    
    Application.ScreenUpdating = False
    
    Lastrow = Range("B" & Rows.Count).End(xlUp).Row
    Range("L2").Formula = "=VLOOKUP(G2,'VLPODL Extract'!A:Q,2,FALSE)"
    Range("L2").AutoFill Destination:=Range("L2:L" & Lastrow)
    Range("M2").Formula = "=VLOOKUP(G2,'VLPODL Extract'!A:Q,17,FALSE)"
    Range("M2").AutoFill Destination:=Range("M2:M" & Lastrow)
    Range("N2").Formula = "=VLOOKUP(A2,'Reason Codes Data'!A:B,2,FALSE)"
    Range("N2").AutoFill Destination:=Range("N2:N" & Lastrow)

    Application.ScreenUpdating = True

If I change the formula to "=IFERROR(VLOOKUP(G2,'VLPODL Extract'!A:Q,2,FALSE),"")" thats what causes it to fall over. Any suggestions?

 

[Fluff]

You need to double up all " inside the formula, you can also get rid of the autofill & use

Range("L2:L" & lastrow).Formula = "=iferror(VLOOKUP(G2,'VLPODL Extract'!A:Q,2,FALSE),"""")"

 

[Joe4]

Most likely the issue is occurring with the literal double-quote marks and your use of them in the formula as text qualifiers - it can get quite confusing as to how it needs to be written.
The good news is, you don't actually need to figure it out yourself - use this little trick.
Turn on your Macro Recorder, and record yourself entering an example of the formula you want in one of the cells, then stop the Macro Recorder, then view the code your just recorded.
You should then be able to just Copy/Paste the formula portion of the code into your code to get your formula working the way it needs to be written (so you're letting Excel figure it out for you!).

 

[Jimmers]

You need to double up all " inside the formula, you can also get rid of the autofill & use

Range("L2:L" & lastrow).Formula = "=iferror(VLOOKUP(G2,'VLPODL Extract'!A:Q,2,FALSE),"""")"

Excellent, thanks ever so much for this.

 

[Fluff]

Glad we could help & thanks for the feedback