# 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

• 35.7 KB Views: 4

#### Endale

##### New Member
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,
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)

#### vletm

##### Excel Ninja
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?

Dear vletm,
PFA.

#### Attachments

• 9.1 KB Views: 4

#### Endale

##### New Member
Dear vletm,
I need to round numbers found on score column to whole number as i have indicated on new value and the sum of new value should be equal to scores sum.

PFA.
Dear vletm,
PFA.

#### Marc L

##### Excel Ninja
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 !​

• Endale

#### Marc L

##### Excel Ninja
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 !

• Endale

#### Marc L

##### Excel Ninja
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 …​