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.

 

[adamsm]

Is this the final code mentioned by you; Tuelor?

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

 

[jasonb75]

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

It's not the formula that appears inefficient, but the code.

Without seeing the full code and knowing what it is you're trying to do it's impossible to say for definite, it's just an opinion based on that line of code.

Immediate observation is that the code will be running far more often than is required.

 

[adamsm]

What I'm trying to do is when the user writes a value in column C of the active sheet, the value related to the value in column C to get copied from the column 11 of the sheet Price range and place it in column H of the active sheet.

If the user writes a value in column C of the active sheet; where this value does not exits in price range sheet I want the column H of the active sheet not to display anything ( meaning not to display the #N/A)

I hope you've now understood my intention.

 

[jasonb75]

And there lies the error in my observation, I saw the variable "nextRow" as a loop, rewriting multiple formula, when it is only writing one.

You could alternatively make use of the Target variable and use something like

Target.Offset(, 5).FormulaR1C1 = "=IFERROR(VLOOKUP(RC3,'Price Range'!R4C1:R51C11,11 ,0,"""")"

Personally I find this method easier when working with formula in code, though it can prove confusing for anyone that doesn't understand R1C1.

 

[adamsm]

Thanks for the recommendation. I do really appreciate it.