# Thread: Round all values up to the nearest \$1 (2003 SP3)

1. ## Round all values up to the nearest \$1 (2003 SP3)

This is a really weird request but I would like to know if there is any possible way to search a giant workbook (the attached one is seriously stripped of data, original file has 50+tabs with lots of rows) for all CURRENCY values and round them all up to the nearest \$1? So for example, on the attached workbook if you look at 3-A, it would look and find the \$5802.81, it would automatically round it to \$5803? On Duplication, the \$0.20 would go up to \$1 etc.

You should also be aware that there are a couple of hidden columns in a few of the sheets on which some of the values are already rounded. For those cases, I need the CURRENT value being depicted in each cell to be rounded. Basically, I can't have the formula on which some of the currency values are based round up after recalculating. It needs to be the current value shown in all cases.

Best case scenario would more than likely be a mass copy/paste values and then a find/replace to the nearest dollar (always rounding up of course) for all currency values. I know it's a strange request but I have a staff member who would like to see the results of this and I'm hoping I don't have to go through and edit all the formulas...

As always, all help is much appreciated.

Thanks.
Pooja

2. ## Re: Round all values up to the nearest \$1 (2003 SP3)

Editted: I did not realise that the values were in a Custom format so this will not work, also some values are less than 50 cents and these would be rounded down to 0, sorry for the curve ball

Hi Pooja

as a quick thought could you not highlight the curreny cells and in the 'Format Cells', 'Number' tab change the currency decimal places to 0, this will round down anything <.50 and round up anything >.50

HTH

Cheers

Steve

3. ## Re: Round all values up to the nearest \$1 (2003 SP3)

The following macro will round up the values of all cells with a numeric value formatted as currency. It doesn't attempt to process the cells that contain text, including those whose text value contains currency values. That would require extensive parsing of the text values.
<code>
Sub RoundEmup()
Dim wsh As Worksheet
Dim rng As Range

For Each wsh In ActiveWorkbook.Worksheets
For Each rng In wsh.UsedRange.SpecialCells( _
xlCellTypeConstants, xlNumbers).Cells
If InStr(rng.NumberFormat, "\$") > 0 Then
rng.Value = Application.RoundUp(rng.Value, 0)
End If
Next rng
Next wsh
End Sub</code>

4. ## Re: Round all values up to the nearest \$1 (2003 SP3)

Hi pooja,

Here's a macro to add a roundup function to all visible currency cells:
<pre>Sub CurrencyRoundup()
Dim wsh As Worksheet
Dim rng As Range
Dim strCell As String
For Each wsh In ActiveWorkbook.Worksheets
For Each rng In wsh.UsedRange.Cells
If InStr(rng.NumberFormat, "\$") > 0 Then
strCell = rng.Formula
If Left(strCell, 1) = "=" Then strCell = Right(strCell, Len(strCell) - 1)
strCell = "=ROUNDUP(" & strCell & ",0)"
rng.Formula = strCell
End If
End If
Next rng
Next wsh
End Sub</pre>

One benefit of this approach is that it preserves the original contents of the cells - just don't run the macro repeatedly. If you're going to need to do that, then you'll probably want to change the code to operate only on the selected range.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•