## Round to 2 Decimal Places

jakjo
Lounger
Posts: 25
Joined: 28 May 2022, 00:57

### Round to 2 Decimal Places

Hello everyone
The following code works fine but I need your support to get the desired results
To clarify things
I have two sheets in my workbook. One is "Customers data" and second one is "Desired results"
In the Customers data sheet, column A contains the customer number and In the output sheet, this number is manually recorded in cell "C5".
My requirement if you change the customer number, this customer's data must be obtained from horizontal to vertical with his personal data as shown in Desired results sheet ..The problem I have when I run this code the numbers end up having 12 or 13 decimal places In columns A, E and G
I only need two decimal places To get the correct results ... Right now, I am wondering ...
Is there a way to edit this to Rounding Decimal Places Correctly? ... I can provide additional clarification if needed
You do not have the required permissions to view the files attached to this post. HansV
Posts: 77254
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Round to 2 Decimal Places

Welcome to Eileen's Lounge!

It's a matter of rounding the "S" amounts to whole numbers:

Code: Select all

``````Sub test()
Dim x, e, r As Range, i As Long, mySum As Double
Application.ScreenUpdating = False
With Sheets("Customers data")
.Range("a8", .Range("a" & Rows.Count).End(xlUp)).Name = "Cust"
x = Application.Match(Sheets("Desired results").[c5], .Range("cust"), 0)
End With
If IsError(x) Then Exit Sub
With Sheets("Desired results")
.[c2] = "=index(offset(cust,,4)," & x & ")"
.[c4] = "=index(offset(cust,,6)," & x & ")"
.[c6:c8] = "=index(offset(cust,,row(a19))," & x & ")"
.[i4:i5] = "=index(offset(cust,,row(a15))," & x & ")"
.[i6] = "=index(offset(cust,,49)," & x & ")"
.[i8] = "=index(offset(cust,,47)," & x & ")"
For Each r In .[c2,c4,c6:c8,i4:i5,i6,i8].Areas
r.Value = r.Value
Next r
With .[a12:b43]
.Formula = Array("=if(b12<>"""",iferror(round(mod(index(offset(cust,,row(a89))," & x & "),1)*100,0),""""),"""")", _
"=iferror(text(rounddown(index(offset(cust,,row(a89))," & x & "),0),""0;-0;""),"""")")
.Value = .Value
End With
mySum = Application.Sum(.[b12:b43]) + Application.Sum(.[a12:a43]) / 100
.Cells(44, "a").Resize(, 2) = Array((mySum - Fix(mySum)) * 100, Fix(mySum))
For Each e In Array(Array(12, 2, 122), Array(14, 21, 128), Array(35, 8, 155))
With .Cells(e(0), "g").Resize(e(1), 2)
.Formula = Array("=if(h" & e(0) & "<>"""",iferror(round(mod(index(offset(cust,,row(a" & e(2) & "))," & x & "),1)*100,0),""""),"""")", _
"=iferror(text(rounddown(index(offset(cust,,row(a" & e(2) & "))," & x & "),0),""0;-0;""),"""")")
.Value = .Value
End With
Next e
x = Array(12, 14, 23, 41, 43)
For i = 1 To UBound(x)
mySum = Application.Sum(.Range("h" & x(i - 1) & ":h" & x(i) - 1)) + Application.Sum(.Range("g" & x(i - 1) & ":g" & x(i) - 1)) / 100
.Cells(x(i) - 1, "e").Resize(, 2) = Array(Round((mySum - Fix(mySum)) * 100, 0), Fix(mySum))
Next i
mySum = [sum(f12:f42)+sum(e12:e42)/100]
.Cells(43, "e").Resize(, 2) = Array(Round((mySum - Fix(mySum)) * 100#, 0), Fix(mySum))
mySum = .[(b44+a44/100)-(f43+e43/100)]
.[e44:f44] = Array(Round((mySum - Fix(mySum)) * 100, 0), Fix(mySum))
End With
Application.ScreenUpdating = True
End Sub``````
Regards,
Hans

jakjo
Lounger
Posts: 25
Joined: 28 May 2022, 00:57

### Re: Round to 2 Decimal Places

That's great. Thank you very much Mr. Hans for your great help and sorry for disturbing you With more edits
I think we need to edit these two lines to rounding Decimal Places in net total Amount

Code: Select all

``````     mySum = .[(b44+a44/100)-(f43+e43/100)]
.[e44:f44] = Array(Round((mySum - Fix(mySum)) * 100, 0), Fix(mySum))

End With
Application.ScreenUpdating = True
End Sub
``````
I mean that if the result of the total net amount as an integer ... In this case, the result of decimal places should be zeros.
The net amounts= Total sales - total discounts
The net amounts is assumed to be = 3300.00 not 3299.100 ... As per the attached example.
Please take a look at the net amount in the two cells ( F44 & E44 ) To find out what I mean
Your time on this is greatly appreciated! HansV
Posts: 77254
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Round to 2 Decimal Places

Change the line

Code: Select all

``````     mySum = .[(b44+a44/100)-(f43+e43/100)]
``````
to

Code: Select all

``    mySum = Round(.[(b44+a44/100)-(f43+e43/100)], 2)``
Regards,
Hans

jakjo
Lounger
Posts: 25
Joined: 28 May 2022, 00:57

### Re: Round to 2 Decimal Places

Now it is perfect and awesome .. Thank you very much Mr. Hans for your great help.
Best Regards