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
please take a look ... Thanks in advance for any help you can provide.
Round to 2 Decimal Places

 NewLounger
 Posts: 22
 Joined: 28 May 2022, 00:57
Round to 2 Decimal Places
You do not have the required permissions to view the files attached to this post.

 Administrator
 Posts: 76154
 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:
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
Hans

 NewLounger
 Posts: 22
 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
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
Is there any additional Conditions should I add to achieve this?
Your time on this is greatly appreciated!
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
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
Is there any additional Conditions should I add to achieve this?
Your time on this is greatly appreciated!

 Administrator
 Posts: 76154
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Round to 2 Decimal Places
Change the line
to
Code: Select all
mySum = .[(b44+a44/100)(f43+e43/100)]
Code: Select all
mySum = Round(.[(b44+a44/100)(f43+e43/100)], 2)
Regards,
Hans
Hans

 NewLounger
 Posts: 22
 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
Best Regards