Empty Cells

  • Thread starter Thread starter jswalsh33
  • Start date Start date
J

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.
 
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))
 
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.
 
Back
Top