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
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
0
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)
0
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.
0
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.
0