counting actual and displayed values
 

Hi guys,
I have a sheet with the following values, and certain cells ar
formatted differently.

0 - displays as 0
0 - displays as 0.0
0 - displays as 0.00
0.0043 - displays as 0.00
0.0043 - displays as 0.0
0.0043 - displays as 0.0043
1 - displays as 1
-0.1 - displays as -0.1

I realize that no matter how a number is formatted, excel retains th
underlying value. I want the formatted value though. For this list o
numbers, I want to count the cells where the underlying value is 0, an
count the cells where the formatted value is 0, via a macro.
So in this example, there are 3 real 0s, and 5 formatted 0s.
I don't really want to convert to text or anything, I tried rounding a
well but can't find a good solution. c.value and c.formula both look a
the underlying value. Is there something I can use in a macro to ge
the displayed value, rather than the underlying value? THanks

--
lxrhe
-----------------------------------------------------------------------
lxrhee's Profile: http://www.excelforum.com/member.php...fo&userid=3452
View this thread: http://www.excelforum.com/showthread.php?threadid=54286

counting actual and displayed values
 

You can test Range("A1").Text for formatted text, and .Value for underlying
value.

HTH
--
AP

"lxrhee" a écrit dans
le message de news: ...

Hi guys,
I have a sheet with the following values, and certain cells are
formatted differently.

0 - displays as 0
0 - displays as 0.0
0 - displays as 0.00
0.0043 - displays as 0.00
0.0043 - displays as 0.0
0.0043 - displays as 0.0043
1 - displays as 1
-0.1 - displays as -0.1

I realize that no matter how a number is formatted, excel retains the
underlying value. I want the formatted value though. For this list of
numbers, I want to count the cells where the underlying value is 0, and
count the cells where the formatted value is 0, via a macro.
So in this example, there are 3 real 0s, and 5 formatted 0s.
I don't really want to convert to text or anything, I tried rounding as
well but can't find a good solution. c.value and c.formula both look at
the underlying value. Is there something I can use in a macro to get
the displayed value, rather than the underlying value? THanks!


--
lxrhee
------------------------------------------------------------------------
lxrhee's Profile:
http://www.excelforum.com/member.php...o&userid=34523
View this thread: http://www.excelforum.com/showthread...hreadid=542865