How to convert all cells that are "#N/A" (result of vlookup) to a blank cell?

I'm pulling numbers into these cells from another worksheet based on an ID number but when the ID number isn't found on the other worksheet, the cell will appear as "#N/A". Is there a code I can enter into the macro that will convert all cells that are "#N/A" into a blank cell?

This is what I'm referring to:

\1

The last column is the sum of all numbers from the first two columns, but if any cell in the first two columns are "#N/A", then it cannot spit out a number in the last column. So I would like to enter a code in VBA to basically converts any cell that is "#N/A" to a blank cell. How do I do that? Thanks

It depends:

- If it's just a presentation thing then select all of the cells on the sheet, add a conditional format based on cell value, where the value is an error and format the text to white

- If you're using Excel 2007 you can encase all of your lookups in IFERROR, e.g. =IFERROR(VLOOKUP(A1,B:C,2,FALSE),"")

- If you're using Excel 2003 you can use ISNA, e.g. =IF(ISNA(VLOOKUP(A1,B:B,1,FALSE)),"",VLOOKUP(A1,B:C,2,FALSE))

- If you're using MS Paint, as per your example, then just colour over the nasty errors with your white crayon.

Originally Posted by Andrew-R

It depends:

- If it's just a presentation thing then select all of the cells on the sheet, add a conditional format based on cell value, where the value is an error and format the text to white

- If you're using Excel 2007 you can encase all of your lookups in IFERROR, e.g. =IFERROR(VLOOKUP(A1,B:C,2,FALSE),"")

- If you're using Excel 2003 you can use ISNA, e.g. =IF(ISNA(VLOOKUP(A1,B:B,1,FALSE)),"",VLOOKUP(A1,B:C,2,FALSE))

- If you're using MS Paint, as per your example, then just colour over the nasty errors with your white crayon.

Well this will be used daily so the numbers that are calculated in the last column need to be correct which means there cannot be any #N/A's in the cells in the first two columns period. If it is not a number, then it needs to be a blank cell.

Originally Posted by tigeravatar

Brookshire,

I would modify the formula to first check if the LookupValue exists. It would look like this:

Please Login or Register  to view this content.

That Lookupvalue, does that need to be changed for every ID number that is being searched? If so then is there some way I can tweak your formula so that I won't have to re-enter it in each cell whenever the search based ID number is changed?

Originally Posted by LaffyAffy13

Please Login or Register  to view this content.

Just an option

Thanks, this worked perfectly.