• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to round numbers based on column value using VBA ?

Endale

New Member
How to round numbers based on column value using VBA ? EX. Column A is Name (person) , B(SCORE) list of score =(12.5 ,6.25,62.5,6.25 and 12.5) , C (expected value after round) =13,6,6,63,12) respectively. i.e sum of B and C should be equal.
 

Attachments

  • Screenshot_20220505-123615_Chrome.jpg
    Screenshot_20220505-123615_Chrome.jpg
    35.7 KB · Views: 4
Endale
Do You mean something like below with formula solution?
D-column shows values with one digit to show/verify real values.
Screenshot 2022-05-05 at 20.18.42.png
For VBA,
is there always five data rows and 100 for sum?
is there that sum row at all?
... because with or without those sums - sums are same in Your sample.
 
Endale
Do You mean something like below with formula solution?
D-column shows values with one digit to show/verify real values.
View attachment 78776
For VBA,
is there always five data rows and 100 for sum?
is there that sum row at all?
... because with or without those sums - sums are same in Your sample.
Dear,
Thank you for asking.
Both the sum of columns and number of raws are different ,thats why i need VBA ,to identify # ,round based on column value. For a single use i can deploy =MIN($B$7-SUM($C$1:C1),ROUND(B2,0)
 
Endale
What are different?
Did You give any answers to my questions?
What did You write about identify #?
Could You send a sample Excel-file, which shows - what do You really expect or have?
 
PFA ?‼​
According to your attachment an Excel basics VBA demonstration for starters :​
Code:
Sub Demo1()
        Dim V, R&, D%
    With Range("B2", [B1].End(xlDown))
        V = .Value
    With Application
            R = .Sum(V)
        If CCur(.Sum(V)) = R Then
            V = .Round(V, 0)
            D = R - .Sum(V)
            If D Then R = .Match(IIf(D < 0, .Max(V), .Min(V)), V, 0): V(R, 1) = V(R, 1) + D
        Else
            V = Empty
        End If
    End With
        If IsEmpty(V) Then Beep: .Columns(2).Clear Else .Columns(2) = V
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
A variation :​
Code:
Sub Demo1v()
        Dim V, R@, D#
    With Range("B2", [B1].End(xlDown))
        V = .Value
    With Application
        R = .Sum(V)
        V = .Round(V, 0)
        D = R - .Sum(V)
        If D Then R = .Match(IIf(D < 0, .Max(V), .Min(V)), V, 0): V(R, 1) = V(R, 1) + D
    End With
       .Columns(2) = V
    End With
End Sub
You may Like it !
 
Original demonstration works only without a column B decimal sum but the variation does not care​
like for example try both demonstrations with 12.4 or 12.6 within cell B2 …​
 
Back
Top