macro to round up (ceiling function)Closed

Question

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



Configuration: Windows XP / Internet Explorer 8.0

Netscur · Answer

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

rizvisa1 · Answer

Would you have value like 3.0 (decimal point) also or would it be 3  (no decimal point)

Netscur · Answer

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.

rizvisa1 · Answer

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

Netscur · Answer

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.

Macro to round up (ceiling function)

5 responses

Similar discussions

Subscribe To Our Newsletter!