Get rid of #N/A

[adamsm]

HI,

The following formula in the worksheet module displays #N/A when no match can be found.

Me.Cells(nextRow, "H").Formula = "=VLOOKUP(C" & nextRow & _
                    ",'Price Range'!A4:K51,11 ,FALSE)"

How could I make the formula not to display the #N/A even no match can be found. Instead leave the column blank.

Any help on this would be kindly appreciated.

Thanks in advance.

 

[Tuelor]

Put an IFERROR statement in front.

D

 

[adamsm]

Thanks for the reply. Should it be as follows?

 Me.Cells(nextRow, "H").Formula = "=IFERROR(VLOOKUP(C" & nextRow & _
                    ",'Price Range'!A4:K51,11 ,FALSE)"

 

[Tuelor]

Nearly, you need a...

,"")


... At the end I belived. This would put in a blank if it errors.

 

[Michael M]

could also use

Me.Cells(nextRow, "H").Formula = "=IF(ISNA(VLOOKUP(C" & nextRow & _
                    ",'Price Range'!A4:K51,11 ,FALSE),"""",VLOOKUP(C" & nextRow & _
                    ",'Price Range'!A4:K51,11 ,FALSE)))"

 

[adamsm]

How about the following version. Is this what you meant?

I would be happy if you could reply me with the whole formula.

                    Me.Cells(nextRow, "H").Formula = "=IFERROR(VLOOKUP(C" & nextRow & _
                    ",'Price Range'!A4:K51,11 ,FALSE),"")"

 

[jasonb75]

Could you tell us exactly what you're trying to do with the code, at first glance it would appear that you're re-writing a range of formula triggered by an event change, which sounds like a waste of system resource.

There may be a more efficient way to do what you're trying.

 

[Tuelor]

Looks good - give it a go...

 

[jasonb75]

Looks good - give it a go...

Me.Cells(nextRow, "H").Formula = "=IFERROR(VLOOKUP(C" & nextRow & _
                    ",'Price Range'!A4:K51,11 ,FALSE),"""")"

looks better, but as I mentioned, it also looks inefficient.

 

[adamsm]

jasonb75. The formula you posted at your last reply seems to be working for me.

But could you define me why you said "it also looks inefficient"?