Empty Cells

[jswalsh33]

When I do a lookup for a specific content of an array of cells that includes
blank cells I get different results if the blank cells have been cleared by
setting them to "" vs cells that I have manually done a "Clear All" command.
These cells are somehow different to the formula
VLOOKUP($C37,Data!$A$5002:$N$9999,$GE37,FALSE), when $C37 is the blank cell
and $GE37 is any number. In the case of the $C37 being cleared with "", I get
a #Value result. In the case where $C37 has been manually cleared with a
"Clear All" command I get an "ISNA" result.

Is there a way with a Macro to clear all blank cells so they give the save
results from the lookup equation.

 

[Shane Devenshire]

Hi,

1. There is nothing in your formula that would cause Excel to return ISNA?
What is in your lookup table for a blank cell? What do you want Excel to do
if the cell is "" verses blank?

You might just write

=IF($C37="","",VLOOKUP($C37,Data!$A$5002:$N$9999,$GE37,FALSE))

 

[jswalsh33]

Your answers allows me to avoid the problem. I would be interested to know
whether clearing the source cell is cleared by entering "" is different
from manually clearing a cell with "Clear All". If so is there a macro that
is the equivalent of "Clear All."

The blank cells in the source cell are created with the formula
IF('C:\SST\[Yellows030309.xls]Yellow
(15)'!$B$30="","",'C:\SST\[Yellows030309.xls]Yellow (15)'!$B$30).

The resulting cell with the "" entry behaves differently than does a cell
that has been cleared with "Clear All"

Thanks for you help.