number format

Av8tordude

Well-known Member
Joined
Oct 13, 2007
Messages
827
I'm having a difficult time understanding why my textbox will not display the last digit.

Cell Value = $1.23932
Cell format = NumberFormat = "_($* #,##0.00000_);_($* (#,##0.00000);_($* ""-""?????_);_(@_)"

Textbox format = Format(Range("H" & ActiveCell.Row), "0.00000")

the number is displayed as 1.23930.


Why is the number the number 2 not showing up????
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,063
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Probably because of the currency cell formatting. Try:

Rich (BB code):
Textbox format = Format(Range("H" & ActiveCell.Row).Value2, "0.00000")
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,737
Office Version
2010
Platform
Windows
Use Format(Range("H" & ActiveCell.Row).Value2, "0.00000")

VBA interprets the cell format as a Currency format. So, VBA rounds the cell to 4 decimal places, when "reading" from the cell into range.Value.

(VBA rounds to 2 decimal places when "writing" into the cell using range.Value, when the cell format is a Currency format.)
 

Av8tordude

Well-known Member
Joined
Oct 13, 2007
Messages
827
Thank you both... that solved the issue. Now to educate myself on Value2 :)
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,737
Office Version
2010
Platform
Windows
The VBA help for range.Value2 is a little misleading, to wit:

``The only difference between this property and the Value property is that the Value2 property doesn’t use the Currency and Date data types. You can return values formatted with these data types as floating-point numbers by using the Double data type``.

It is not that it does not "use" (sic) the Currency and Date types. And it is not that we can or must "use" type Double.

It is that with range.Value2, cells formatted as type Currency and Date and variables that are type Currency and Date are not treated differently than type Double.

I cannot find where VBA help explains the special treatment of range.Value.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,033
Messages
5,472,105
Members
406,804
Latest member
xbinsx

This Week's Hot Topics

Top