0

I have an input field where the user inputs a number "X.XXXXX"

I then copy that number using some VBA to another sheet when a button is pressed. The problem occurs when the number ends in 0 or multiple zeros. For example, take the number 5.46770. I have the cell formatted to display 5 decimal places. However, if the trailing number is a 0, Excel still considers the value to be 5.4677 unbeknownst to the user. So when my macro pull the value from the cell it takes 5.4677 vs. 5.46770. What I'm trying to figure out is how to have my VBA code pull the trailing 0(s). Any ideas?

Improve this question
3
  • Is it just the destination formatting you want to be 5 decimal places? I guess my question is what are you losing from a calculation standpoint. Commented Sep 18, 2013 at 20:02
  • Well, the source formatting is already 5 decimal places because I set the cell that the user inputs to as a number with 5 decimal places. No calculation is done with the number. It is concatenated to the end of a string to create a note. Commented Sep 18, 2013 at 20:06
  • Ah I see where @Richard is coming from now. You can have it input as 5 decimals, format is as text, and then when concatenated it would have all 0's. Commented Sep 18, 2013 at 20:10

2 Answers 2

Reset to default
0

The value of 5.4677 is the same as 5.46770; they are equal.

If you are trying to get a string that is formatted just like the cell, try:

cell.Text
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Won't that just take the actual value, not the display? The value doesn't have the 0's in the first place? EDIT: The cell would have to be formatted to text before the number is entered, or else the 0's don't show.
@Joe I ran a quick test with a value of 123.4 in a cell that is number formatted to 0.00 and the cell.Text evaluated to "123.40". The actual value is in cell.Value. Maybe I am doing something wrong here.
0

Depending on what you are doing with the NUMBER, you could set the cell value with a single quote in front. It will force it to be shown like text, leading/trailing zeros or not. IE:

CELL2.VALUE = "'" & FORMATNUMBER(CELL1.VALUE,5)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.