Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Issue: Range A1:Z365 contains numbers. Is there any formula or code that would round all of the numbers to zero decimal places without having to go into each cell individually?
    Thanks in advance.
    Jeff

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Select the range and then run this code:

    Code:
    Option Explicit
    Sub RoundSelection()
      Dim rCell As Range
      For Each rCell In Selection
    	rCell.Value = Round(rCell.Value, 0)
      Next
      Set rCell = Nothing
    End Sub

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='775885' date='19-May-2009 10:19']Select the range and then run this code:

    Code:
    Option Explicit
    Sub RoundSelection()
      Dim rCell As Range
      For Each rCell In Selection
    	rCell.Value = Round(rCell.Value, 0)
      Next
      Set rCell = Nothing
    End Sub

    Steve[/quote]

    Thanks Steve, that works great! I know I've seen a post on here somewhere that would actually insert the argument ROUND(XX,YY) into a selected or highlighted range. Do you recall?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You can use something like:

    rCell.Formula = "=Round(" & rCell.Value & ", 0)"

    Steve

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='775889' date='19-May-2009 10:39']You can use something like:

    rCell.Formula = "=Round(" & rCell.Value & ", 0)"

    Steve[/quote]

    Yep-that was it!
    Thanks again.

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='jlkirk' post='775891' date='19-May-2009 08:40'][/quote]
    A fancier version I use lets you select number of rounding digits:
    [codebox]Sub MakeRound()
    Dim rngCell As Range
    Dim wsWksht As Worksheet
    Dim strFormula As String, strDigits As String
    Dim intCalcSet As Integer

    Application.ScreenUpdating = False
    intCalcSet = Application.Calculation
    Application.Calculation = xlCalculationManual

    strDigits = InputBox("How many digits? ")
    For Each wsWksht In ActiveWindow.SelectedSheets
    If Not wsWksht.ProtectContents = True Then
    For Each rngCell In Selection
    If rngCell.HasFormula And Left$(rngCell.Formula, 6) <> "=ROUND" Then
    strFormula = "=ROUND(" & Right$(rngCell.Formula, Len(rngCell.Formula) - 1) _
    & "," & strDigits & ")"
    rngCell.Formula = strFormula
    End If
    Next
    End If
    Next wsWksht
    Application.Calculate
    Application.Calculation = intCalcSet
    Application.ScreenUpdating = True
    End Sub[/codebox]
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='jlkirk' post='775891' date='19-May-2009 10:40']Yep-that was it!
    Thanks again.[/quote]

    Thanks John. Unfortunately, the code I entered doesn't work:

    Sub MakeRound()
    Dim rngCell As Range
    Dim wsWksht As Worksheet
    Dim strFormula As String, strDigits As String
    Dim intCalcSet As Integer
    Application.ScreenUpdating = False
    intCalcSet = Application.Calculation
    Application.Calculation = xlCalculationManual
    strDigits = InputBox("How many digits? ")
    For Each wsWksht In ActiveWindow.SelectedSheets
    If Not wsWksht.ProtectContents = True Then
    For Each rngCell In Selection
    If rngCell.HasFormula And Left$(rngCell.Formula, 6) <> "=ROUND" Then
    strFormula = "=ROUND(" & Right$(rngCell.Formula, Len(rngCell.Formula) - 1) _
    & "," & strDigits & ")"
    rngCell.Formula = strFormula
    End If
    Next
    End If
    Next wsWksht
    Application.Calculate
    Application.Calculation = intCalcSet
    Application.ScreenUpdating = True
    End Sub

    Any ideas?
    Thanks again.

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='jlkirk' post='775922' date='19-May-2009 10:25']Unfortunately, the code I entered doesn't work. Any ideas?[/quote]
    It worked for me from your listing. See if the attachment helps with the code.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    [quote name='jlkirk' post='775880' date='19-May-2009 14:22']Issue: Range A1:Z365 contains numbers. Is there any formula or code that would round all of the numbers to zero decimal places without having to go into each cell individually?
    Thanks in advance.
    Jeff[/quote]


    Just a thought:

    Perhaps you can use Tools->Options->Edit and then tick the checkbox for Fixed decimal places, set to zero.
    No code and no formulas.
    You can then untick the checkbox if required.

    zeddy

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='JohnBF' post='775926' date='19-May-2009 12:42']It worked for me from your listing. See if the attachment helps with the code.[/quote]

    John,
    Here is a test file. I still couldn't get it to run.
    Thanks for your help.
    Jeff
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't see any code in your workbook.

    (This might be because I'm opening it using the file converter - I don't have Excel 2007)

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='775950' date='19-May-2009 14:01']I don't see any code in your workbook.

    (This might be because I'm opening it using the file converter - I don't have Excel 2007)[/quote]

    Hans,
    I just opened it up from the website and the code is located in the Personal.xls workbook.
    Thanks

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='jlkirk' post='775951' date='19-May-2009 21:05']Hans,
    I just opened it up from the website and the code is located in the Personal.xls workbook.
    Thanks[/quote]
    Unfortunately, I don't have access to your Personal.dot at this particular moment, so I can't tell you why the code fails.

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='775952' date='19-May-2009 14:06']Unfortunately, I don't have access to your Personal.dot at this particular moment, so I can't tell you why the code fails.[/quote]
    Thanks. Interesting note: I just tried it on an 03 filr and it worked just fine!

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The code only works on cells containing a formula. Your sample sheet doesn't contain formulas, only constant values.

Page 1 of 2 12 LastLast

Posting Permissions

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