CODEWITHSUNDEEP
vbaexcel
newkid59
newkid59

Reputation: 19

IFERROR or Skipping #NA's in VBA

So I have this code below, but I want to leave any cell that comes up with
"#N/A" as a blank value. 

Range("R2").Select
ActiveCell.FormulaR1C1 = _
    "=INDEX(Detail!C[-17]:C[-17],MATCH(C[-16]&""D&O"",Detail!C[-14]:C[-14],0),1)"
Range("R2").AutoFill Destination:=Range("R2:R" & lastrow)

I know that I can fix that when I type directly in the cell by using =IFERROR(INDEX(Detail!A:A,MATCH(B:B&"D&O",Detail!D:D,0),1),""), but I want it to automatically populate the whole column through VBA, and if I use IFERROR in the VBA I get a Run-time error '1004': Application-defined or object-defined error.

Any ideas on how I can update my code to populate the #NA's as blank cells?

Upvotes: 0

Views: 235

Answers (2)

Ajeet Shah
Ajeet Shah

Reputation: 19813

You can also try:

ActiveCell.FormulaR1C1 = "=IFERROR(INDEX(Detail!C[-17]:C[-17],MATCH(C[-16]&""D&O"",Detail!C[-14]:C[-14],0),1),""Error"")"

Upvotes: 0

Paaqua Grant
Paaqua Grant

Reputation: 160

First, this could be considerably simplified by changing it to:

Range("R2:R" & lastrow).FormulaR1C1 = _ 
"=IFERROR(INDEX(Detail!C[-17]:C[-17],MATCH(C[-16]&""D&O"",Detail!C[-14]:C[-14],0),1),"""")"

No need to .Select and then .AutoFill to your range. Also, from the body of your question, it looks like you didn't double-quote your double-quotes (4 consecutive double-quotes).

This worked just fine for me.

Upvotes: 3

Related Questions