Error: If Range("AZ" & intCounter).Text = "" Then

[staeri]

I have a very strange error in Excel 2003:

All cells in column AZ are empty. When I run this code:

If Range("AZ" & intCounter).Text = "" Then

it says that the cells contain the value TRUE.

How can this be possible?

Very grateful for help!

// S

 

[Mike H]

Hi,

I'm not sure I fully understand what you mean but if you apply the test

If Range("AZ" & intCounter).Text = "" Then

to the cells and they are empty then that will evaluate as TRUE, it doesn't
mean the cell contains the value TRUE. For example try this small routine on
your cells

Sub nnn()
For intcounter = 1 To 20
If Range("AZ" & intcounter).Text = "" Then
MsgBox Range("AZ" & intcounter).Text = ""
Else
MsgBox Range("AZ" & intcounter).Text = ""
End If
Next
End Sub

Empty cell evalute as TRUE and populated cells evaluate as FALSE

Mike

 

[Mike H]

I should add that if a cell in your range contains a formula such as

=if(A1="","","")

which would always return a null string then your test would evaluate as
TRUE for this cell despite it containing a formula.

Mike

 

[Nigel]

You are applying a logical test...... if is = "" ....which will evaluate
TRUE, if the cell is empty, FALSE otherwise.

 

[staeri]

After some more testing I've found that the problem is that
"Range("AZ" & intCounter).Text" returns nothing if the cell content
only contains numbers. If I change to "Range("AZ" & intCounter).Value"
it returns the content also if the content only is a number. Why
doesn't it return the content with .Text?

// S

 

[Nigel]

Because .text returns a string of what is displayed, .values returns a
variant of the cell contents the thing you are testing.

--

Regards,
Nigel
(e-mail address removed)



After some more testing I've found that the problem is that
"Range("AZ" & intCounter).Text" returns nothing if the cell content
only contains numbers. If I change to "Range("AZ" & intCounter).Value"
it returns the content also if the content only is a number. Why
doesn't it return the content with .Text?

// S

 

[Mike H]

Hi,

I'm not sure how you tested and came up with the result you did but

MsgBox Range("AZ" & intcounter).Text

Will return a number if that's what's in the cell

Mike

 

[Dave Peterson]

And it's not just the value that's important--it's what you see in the cell--not
the formulabar.

You could see ####'s returned (in .text) if the columnwidth isn't wide enough.

You could see nothing (in .text) if the number format hides the value (custom
format of ;;; for instance).

But if you can see something in the cell, you should see something returned with
..text.