why does VBA this statement ignore 0 values - it shouldnt

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
1,302
Hi All

Hoping you can help me understand why this is ignoring 0 values when it shouldnt - it is a number and ive tested it

If c.Value <> "" And c.Value <> Empty And c.Value <> "Ok" And c.Value <> "Not Ok" Then
'do something

Thank You
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,610
Office Version
365, 2016
Platform
Windows
If you remove c.Value <> Empty then the if statement is true. Looks like the 0 is being seen as empty.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,541
Office Version
365
Platform
Windows
What do you mean by 'ignoring'?
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
1,302
Hi Norie - it was not including 0s a number and treating it as empty
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
Code:
If c.Value <> "" And Not IsEmpty(c.Value) And c.Value <> "Ok" And c.Value <> "Not Ok" Then
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
1,302
Hi SHG - thank you

ill give that a go - whats the difference between

Not IsEmpty(c.Value)

and

C.value <> empty
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
VBA coerces 0 to Empty for comparison purposes. IsEmpty tests the Variant Type in the cell, which is always String, Boolean, Double, Error, or Empty.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,984
Messages
5,447,700
Members
405,463
Latest member
Tommy5

This Week's Hot Topics

Top