VBA question

[tispivey]

Hello all,

I have a possibly stupid question. I can get a formula to work for what I want, but when I attempt to add it to a macro that I have, I get an error.

The formula is =IFERROR(IF(VALUE(N2)=$BD$1," ",VLOOKUP(VALUE(LEFT(N2,3)),BD:BE,2,0)),"")

Which essentially looks up a code, compares it to a list I have in the spreadsheet, then returns something from another list, then if there is an error, it returns nothing. This works in the spreadsheet, but when I attempt to put that same formula in the VBA code I have, it breaks.

This is the code that works, before the IFERROR:

Range("ag" & I).Formula = "=IF(VALUE(n" & I & ")=$bd$1,"" "",VLOOKUP(VALUE(LEFT(n" & I & ",3)),BD:BE,2,0))"

But as soon as I attempt to add the Iferror, it breaks. Is there a different way to add iferror to a code in VBA?

This is for work, and has customer info, so I am not sure how much I can put in here, but if it is needed, I can try.

 

[Fluff]

What was the formula that didn't work & in what way didn't it work?

 

[tispivey]

This works in VBA:

Range("ag" & I).Formula = "=IF(VALUE(n" & I & ")=$bd$1,"" "",VLOOKUP(VALUE(LEFT(n" & I & ",3)),BD:BE,2,0))"

But, what I want to do is like an IFERROR then leave a blank cell.

Is there a different want to put this in VBA than in excel?

 

[Fluff]

This works in VBA:

That is not what I asked for & you didn't answer my other question either.

 

[tispivey]

That is not what I asked for & you didn't answer my other question either.

Sorry, missed that.

Range("ag" & I).Formula = "=IFERROR(IF(VALUE(n" & I & ")=$bd$1,"" "",VLOOKUP(VALUE(LEFT(n" & I & ",3)),BD:BE,2,0)),"")"

This code works when I manually put it in Excel, but wont in VBA. The error I get is: Run-time error '1004': Application-defined or object-defined error

 

[Fluff]

Ok, thanks for that, you need to double-up on the quotes for the error value.

 

[tispivey]

That worked! Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.