VBA Code to roundup a number

[Darrell MacDonald]

Hello
I'm hoping someone can provide me with code that will round up the value in each cell in a column of cells. For example, could it take a cell with "23,937,012" and make it be "=roundup(23,937,012,-5)", then bump down to the next cell, do the same with that particular number and continue down to the last number with a value in it?

Thanks in advance.

 

[Z51]

This sample code will take the used range in column A (A1:A???) and perform the round up for each cell, then replace A1:A??? with the new values.

Sub DMac()
Dim arr() As Variant, i As Long, lastrow As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
arr = Range("A1:A" & lastrow)

For i = 1 To UBound(arr)
    arr(i, 1) = WorksheetFunction.RoundUp(arr(i, 1), -5)
Next i

Range("A1").Resize(UBound(arr)).Value = arr
End Sub

 

[Darrell MacDonald]

Z51
Thanks so much for taking the time to write that code. Is there a way to make it work on a chosen column of values? This version works beautifully if the range is from A1:?.

 

[Fluff]

Is there a way to make it work on a chosen column of values?

Will that column always be the same? If not how would you choose it?

 

[Darrell MacDonald]

Fluff
There are several columns that I'd like this run on. Could I click on the top cell and have the routine start there and do what Z51's code currently does to column A? Then, I'd click on a few more. Here's a screenshot of the columns.

 

[Fluff]

Ok, how about

Sub Darrell()
   With Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp))
      .Value = Evaluate(Replace("if(@="""","""",if(istext(@),@,roundup(@,-5)))", "@", .Address))
   End With
End Sub

 

[Darrell MacDonald]

Wow! Fluff, that's awesome!! Thanks so much to both you and Z51. I'm always impressed by the kindness of this board's members.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Darrell MacDonald]

Fluff. Sorry to be a pain but there's one thing I just noticed and didn't mention to you earlier. Each of the cells contains a formula and isn't just a number. For example, one of them is:
='PP-6 400'!$M$278

The current code removes the formula and turns it into an integer. Is there a way to allow the formula to remain like so: =roundup('PP-6 400'!$M$278,-5)

 

[Fluff]

That is a completely different thing, so it needs a new thread.