Blank Cell Oddity
 

I have a spreadsheet that shows either a price or a discount (never has both)
based on given quantities. I have a formula that goes and looks at each cell
to determine which to use. I am using the IFBLANK function. If the price
cell is blank, it applies a discount to another number in the sheet. If the
price cell is not blank, it uses that price. The problem I have is that some
cells it considers to not be blank are blank. I can copy and paste a "blank"
cell to another cell and there is nothing in there. If I click a random cell
and type ="blank cell #", it is blank. Now, if I double click that cell and
hit enter (without entering anything), it then treats the cell as blank. I
have over 14,000 lines and about 8 rows of this data that I would like to
avoid having to double click each blank row to get this to work. Any
suggestions?

Blank Cell Oddity
 

Don't test for blanks using ISBLANK()

=IF(ISBLANK(A1),.95*Z100,A1)

instead use:

=IF(A1="",.95*Z100,A1)

The ISBLANK() function is misnamed. It should have been called ISEMPTY()


For example, if you enter:
="" in A1
ISBLANK(A1) will report FALSE!!
--
Gary''s Student - gsnu200856


"Zeke" wrote:

I have a spreadsheet that shows either a price or a discount (never has both)
based on given quantities. I have a formula that goes and looks at each cell
to determine which to use. I am using the IFBLANK function. If the price
cell is blank, it applies a discount to another number in the sheet. If the
price cell is not blank, it uses that price. The problem I have is that some
cells it considers to not be blank are blank. I can copy and paste a "blank"
cell to another cell and there is nothing in there. If I click a random cell
and type ="blank cell #", it is blank. Now, if I double click that cell and
hit enter (without entering anything), it then treats the cell as blank. I
have over 14,000 lines and about 8 rows of this data that I would like to
avoid having to double click each blank row to get this to work. Any
suggestions?

Blank Cell Oddity
 

I have found one way to do this is to check for the dreaded space bar. find
a cell and touch the spacebar to see what I mean. This will clear the cells
or use the check within your code
If Len(Application.Trim(Cells(i + 1, mc))) < 1

mc=3 'col c
for i=1 to cells(rows.count,mc).end(xlup).row
If Len(Application.Trim(Cells(i + 1, mc))) < 1 _
Then Cells(i + 1, mc) = ""
next i
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Zeke" wrote in message
...
I have a spreadsheet that shows either a price or a discount (never has
both)
based on given quantities. I have a formula that goes and looks at each
cell
to determine which to use. I am using the IFBLANK function. If the price
cell is blank, it applies a discount to another number in the sheet. If
the
price cell is not blank, it uses that price. The problem I have is that
some
cells it considers to not be blank are blank. I can copy and paste a
"blank"
cell to another cell and there is nothing in there. If I click a random
cell
and type ="blank cell #", it is blank. Now, if I double click that cell
and
hit enter (without entering anything), it then treats the cell as blank.
I
have over 14,000 lines and about 8 rows of this data that I would like to
avoid having to double click each blank row to get this to work. Any
suggestions?

Blank Cell Oddity
 

Hi,

You get different results depending on how you test, for example:
=ISBLANK(E12)
=COUNTBLANK(E12)
=IF(E12="","yes","no")

A cell with a formula that evaluates to "" is not considered blank by the
ISBLANK function, but is by the other two. A formula containing a spacebar
looks blank but none of these formulas will see it as blank. Then there is
the issue of hidden characters which may get into your spreadsheet when you
import data from an external source (or copy it).

Find and Replace can be used in many but not all cases to take care of the
problem. If the problem is spacebars then you can type a spacebar in the
Find what box of the Find & Replace dialog box and leave the Replace with box
empty, check Match entire cell contents under Options and Replace All.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Zeke" wrote:

I have a spreadsheet that shows either a price or a discount (never has both)
based on given quantities. I have a formula that goes and looks at each cell
to determine which to use. I am using the IFBLANK function. If the price
cell is blank, it applies a discount to another number in the sheet. If the
price cell is not blank, it uses that price. The problem I have is that some
cells it considers to not be blank are blank. I can copy and paste a "blank"
cell to another cell and there is nothing in there. If I click a random cell
and type ="blank cell #", it is blank. Now, if I double click that cell and
hit enter (without entering anything), it then treats the cell as blank. I
have over 14,000 lines and about 8 rows of this data that I would like to
avoid having to double click each blank row to get this to work. Any
suggestions?