Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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
    If Range(rng.Address).Columns.Hidden = False 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.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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