Macro to round up (ceiling function)

Closed
Netscur Posts 21 Registration date Thursday July 23, 2009 Status Member Last seen April 6, 2012 - Aug 18, 2010 at 03:24 PM
Netscur Posts 21 Registration date Thursday July 23, 2009 Status Member Last seen April 6, 2012 - Aug 23, 2010 at 11:50 AM
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

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Aug 23, 2010 at 09:18 AM
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
Netscur Posts 21 Registration date Thursday July 23, 2009 Status Member Last seen April 6, 2012
Aug 19, 2010 at 07:27 AM
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
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Aug 20, 2010 at 09:25 AM
Would you have value like 3.0 (decimal point) also or would it be 3 (no decimal point)
0
Netscur Posts 21 Registration date Thursday July 23, 2009 Status Member Last seen April 6, 2012
Aug 23, 2010 at 08:43 AM
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

Didn't find the answer you are looking for?

Ask a question
Netscur Posts 21 Registration date Thursday July 23, 2009 Status Member Last seen April 6, 2012
Aug 23, 2010 at 11:50 AM
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