Macro to round up (ceiling function)

[Closed]
Report
Posts
21
Registration date
Thursday July 23, 2009
Status
Member
Last seen
April 6, 2012
-
Posts
21
Registration date
Thursday July 23, 2009
Status
Member
Last seen
April 6, 2012
-
Hello,

I have a bunch of raw data [in cell range $B$2:L499] I want a macro which will read each of the cells and re-populate with Integers.

I've tried a few tips from other sites but nothing has worked.
Any advice is appreciated.

-Netscur



5 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
There is an inbuilt function called RoundUP in excel that you can use.
=ROUNDUP(RAND()*100,0)


You mentioned earlier that you want to "round up". But in sample data it looked like as being truely rounded . Presuming you want to "round up", you can also try this

Sub RoundUp()
Dim Cell As Range
Dim lMaxRows As Long
Dim iMaxCols As Integer

    Set Cell = Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    lMaxRows = Cell.Row
    
    If lMaxRows < 2 Then GoTo Exit_Sub
    
    Set Cell = Cells.Find("*", Cells(1, 1), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
    iMaxCols = Cell.Column
    
    If iMaxCols < 2 Then GoTo Exit_Sub
    
    For Each Cell In Range(Cells(2, 2), Cells(lMaxRows, iMaxCols))
        
        Cell.Value = CLng(Cell.Value + 0.49)
        
    Next
    
Exit_Sub:
    Set Cell = Nothing
    
End Sub
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
21
Registration date
Thursday July 23, 2009
Status
Member
Last seen
April 6, 2012

I also could have been more specific about my raw data. Some but not all of it contains fractions (or decimal places).

Raw Data Example: 3.00011 which I want to be "rounded up" to be 4
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Would you have value like 3.0 (decimal point) also or would it be 3 (no decimal point)
Posts
21
Registration date
Thursday July 23, 2009
Status
Member
Last seen
April 6, 2012

It is possible in my original data to have a 3.0 as a number or value.



After the macro is run, ideally the Number Format won't matter as long as each piece of raw data is a complete integer.

This path is to a version of what I have so far:
https://authentification.site/files/23928300/Crosstab2tab_macro_workbook_-_shared.xlsm

Within the example,

1) Worksheet 1 ("Rawdata") is where a user can paste a forecast of values that are in a cross tabular format. Currently for testing purposes, the Rawdata worksheet is populated by random numbers.

2) Worksheet 2 ("Sheet2") is the output of the existing macro which transposes the format of cross tabular ("Rawdata") to tabular format.
Posts
21
Registration date
Thursday July 23, 2009
Status
Member
Last seen
April 6, 2012

Thanks for your help again Rizvisa1!!! Your macro accomplished what I was hoping.






FYI: After some more trial & error, I wrote this and it seems to work well too


Sub Ceiling()

Range("B2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection = Application.Ceiling(Selection, 1)

End Sub



'Basically it selects all the cells from $b:$2 to end of worksheet. Then it applies the 'ceiling' function to what was selected.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!