1

I have cells that are formatted 0.00%. When typed in by the user it formats fine But if a cell is populated by a calculation done in vba it doesn't limit to 2 decimal places but goes like 33.3333333333. Is there a way to have it conform to what the cell is formatted as? thanks. cgeddes

B5 changed by downpayment change

    Case "$D$5" ' Downpayment Entered
        Me.Unprotect
        range("B5") = (range("D5").Value / range("D4").Value) * 100
        Debug.Print "Percent " & range("B5").Value
        range("D6").Value = (range("D4").Value - range("D5").Value)
          If range("D21") <> 0 Then
       MsgBox "The Total Mortgaged Amount has changed, The Mortgage Payment Amount (Cell D21) is no longer valid. Please Re-calculate Mortgage with New Amount"
       End If

The formatting on B5 is 0.00##\% When a user enters is displays as 5.00% or 10.00% whatever they enter.

Hmmm. Maybe the ## means something ?? Thank you Tim for helping be again today.

Slai
  • 22,144
  • 5
  • 45
  • 53
geddeca
  • 119
  • 1
  • 1
  • 9
  • Exactly how is the cell being populated? Showing your code always helps with answers. – Tim Williams Dec 14 '16 at 22:19
  • Possible duplicate of [What are .NumberFormat Options In Excel VBA?](http://stackoverflow.com/questions/20648149/what-are-numberformat-options-in-excel-vba) – Mathieu Guindon Dec 14 '16 at 22:27
  • It probably has to do with *how* the vba code is changing the cell values as a result of using the different cell properties. Take a look at: http://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2 – Assaf Dec 14 '16 at 22:33
  • `#` is used to display non-zero digits. The number Format doesn't affect the actual cell value, but only how it's displayed. You can use `.Text` instead of `.Value` if you want to get the formatted value instead of the actual cell value. – Slai Dec 15 '16 at 00:59
  • I put in the code and 2 images. the first how it formats when user puts numbers in. the second - what happens when the cell is calculated using the vba code. sometimes it is a massive amount of decimal places 20?? – geddeca Dec 15 '16 at 01:19
  • Simple solution buddy - just add to the VBA code right before you update the value `range("B5").NumberFormat = "0.00"` Alternatively, you can wrap the line in `Round((range("D5").Value / range("D4").Value) * 100,2)` – user1274820 Dec 15 '16 at 01:33
  • Slai, Thank you for your input. I am just wondering, would changing the value to .text not stop my calculations from working? – geddeca Dec 15 '16 at 18:47
  • 1
    user1274820 - I just see this now. Why wasn't it visible earlier? OK maybe I am going blind and didn't see it. Thank you again for your great answer – geddeca Dec 15 '16 at 18:48

1 Answers1

0

As I said in my comment, if you want to change the number format, use:

range("B5").NumberFormat = "0.00\%"
range("B5") = (range("D5").Value / range("D4").Value) * 100

If you want to actually round the number, use

range("B5") = Round((range("D5").Value / range("D4").Value) * 100,2)

The parameters of Round are: Round(Number, [Decimal Places])

user1274820
  • 7,786
  • 3
  • 37
  • 74
  • 1
    User1274820, I am sorry, I didn't see your original comment. I still don't. I used your solution and it worked perfect. Thank you. – geddeca Dec 15 '16 at 18:44