VBA getting rid of #N/A

[tintinoo7]

Hi

I have the following code that works fine, but if the Data in Sheet "Data" does not have a match it returns #N/A. I tried code to find text and change cell colour but it does not find #N/A. The next bit of code is a copy and paste special (values) so I have got rid of the formula in the cells. Please help me either come up with a way to remove #N/A and replace it with a cell highlighted yellow or just a way to get rid on #N/A. Thanks

With ThisWorkbook.Worksheets("Imp")

.Range("G2:G" & lr) = "=VLOOKUP(E2,Data!E:G,2,FALSE)"
.Range("H2:H" & lr) = "=VLOOKUP(E2,Data!E:G,3,FALSE)"
.Range("I2:I" & lr) = "=VLOOKUP(B2,B:B,1,FALSE)"

End With

 

[Fluff]

Hi & welcome to MrExcel
This will remove the errors

.Range("G2:G" & Lr) = "=VLOOKUP(E2,Data!E:G,2,FALSE)"
.Range("H2:H" & Lr) = "=VLOOKUP(E2,Data!E:G,3,FALSE)"
.Range("I2:I" & Lr) = "=VLOOKUP(B2,B:B,1,FALSE)"
[COLOR=#0000ff].Range("G2:I" & Lr).SpecialCells(xlFormulas, xlErrors).ClearContents
[/COLOR]

 

[tintinoo7]

Fluff

Thank you so much what an incredible response time.

Now how to make the empty cells highlighted as yellow?

Regards

tintinoo7

 

[Fluff]

Add this line

[COLOR=#0000ff].Range("G2:I" & Lr).SpecialCells(xlFormulas, xlErrors).Interior.Color = vbYellow[/COLOR]
.Range("G2:I" & Lr).SpecialCells(xlFormulas, xlErrors).ClearContents