Round to 2 Decimal Places

[jakjo]

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.

[HansV]

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

[jakjo]

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

[HansV]

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)

[jakjo]

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