OzGrid

How to create a macro to round total based on a cell value

Requirement:

The user has a workbook which invoice values need to rounded based on cell value M2.

The column E represents the values of invoice and it total 11307.92 which represents the original value and column F is the the round value but on copy and paste special values on column F gives total amount 11306 instead of 11308.

So the user has  to pick up some cell values at random on column F so that total =11308 as cell M2.

However there are times when value might be more 11312 so I need to select at random on column F so that =11308 as example .

The values total varies , it can be more or sometimes less.

The user is looking a macro to do this as data is dynamic up to 3000 rows.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1201162-macro-to-round-total-based-on-a-cell-value

Solution:

With Cell F17 change

=SUM(F2:F16)

to

=ROUND(E17,0)

and format that cell as number with 0 decimal places.

To do so by macro try this:  Clear the totals you have in cells E17 and F17 then run this

Sub RoundTotal()
    Cells(2, 13) = Round(Application.Sum(Cells(1).CurrentRegion.Offset(1).Columns(5)))
End Sub

That will be dynamic for any number of rows of data.

This would be better as it will account for the possibility of the Total and Rounded Total already being present at the end of the data.

Sub TotalAndRoundTotal()

    With Cells(1).CurrentRegion.Offset(1)
        If Application.Count(.Rows(.Rows.Count - 1)) = 2 Then
            .Rows(.Rows.Count - 1).Clear
            .Cells(.Rows.Count - 1, 5).Resize(, 2) _
                = Array(Application.Sum(.Columns(5)), Round(Application.Sum(.Columns(5))))
            .Cells(.Rows.Count - 1, 6).NumberFormat = "0"
        Else
            .Cells(.Rows.Count, 5).Resize(, 2) _
                = Array(Application.Sum(.Columns(5)), Round(Application.Sum(.Columns(5))))
            .Cells(.Rows.Count, 6).NumberFormat = "0"
        End If
    End With
        
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by KjBox.

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

See also:

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery