Macro to round values

[RR Guy]

I need a macro to round a value in a cell.

I have a macro that will round a formula in a cell. However I want a macro to simply round 7.28 to 7 in the cell(s) I select. I dont want to add the "Round" formula, I simply want to have the macro change the cell from 7.28 to 7.

I have used a similar macro before, but my computer crashed and all of my macro's were lost (I know I should have backed it up).



Can someone help me out. I have searched hundreds of threads and can't seem to find the correct formula for this.

Thanks in advance.

 

[Scott Huish]

Sub RoundNumbers()
Dim c As Range
For Each c In Selection
    If IsNumeric(c) And c <> "" Then c = Int(c)
Next
End Sub

 

[bstory84]

Throw this in the Sheet module.
This will round any cell you double click.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Target = Round(Target, 0)
End Sub

 

[Zack Barresse]

RR Guy,

Can you define how you want to round? Since there are many ways, and if these solutions don't work in all situations, you'll need to define you round parameters. The above two solutions can act differently.

 

[RR Guy]

RR Guy,

Can you define how you want to round? Since there are many ways, and if these solutions don't work in all situations, you'll need to define you round parameters. The above two solutions can act differently.

My old macro allowed me to select a cell or cells and would round them to the nearest whole number. For instance 7.2 would round to 7 and 7.5 would round to 8. I then modified that macro so that I could use it to when I was working with larger numbers I could round to the nearest 10 or 100 depending on which macro I selected. I placed these in my personal.lxb file and could use them in any worksheet. I have a Macro written that goes in and adds the "Round" function to a cell that I have selected that has a formula in it, but I need a macro to help me round for cells that only have values in them. I hope I am making sense.

Thanks!

 

[RR Guy]

Sub RoundNumbers()
Dim c As Range
For Each c In Selection
    If IsNumeric(c) And c <> "" Then c = Int(c)
Next
End Sub

Is there a way to modify this so I can create a second macro to round to the nearest 10 or even 100?

 

[Scott Huish]

Sub RoundNumbers()
Dim c As Range, e As Long
e = 0
For Each c In Selection
    If IsNumeric(c) And c <> "" Then c = Application.WorksheetFunction.Round(c, e)
Next
End Sub

This uses the worksheet ROUND function and so it takes the same parameters.

Change e to -1 to round to 10s, -2 to 100s, etc.

I've never liked how Microsoft made the ROUND function though, up to you but since we're customizing it. I always have thought that the second parameter should be a power of 10. So -2 would be 10^-2 or the 100ths place. 100s would be 10^2 If you want it that way:

This would round to 100s, If e were -2 it would round to 100ths.

Sub RoundNumbers()
Dim c As Range, e As Long
e = 2
For Each c In Selection
    If IsNumeric(c) And c <> "" Then c = Application.WorksheetFunction.Round(c, -e)
Next
End Sub

 

[RR Guy]

Thank you!! This is exactly what I needed.

Sub RoundNumbers()
Dim c As Range, e As Long
e = 0
For Each c In Selection
    If IsNumeric(c) And c <> "" Then c = Application.WorksheetFunction.Round(c, e)
Next
End Sub

This uses the worksheet ROUND function and so it takes the same parameters.

Change e to -1 to round to 10s, -2 to 100s, etc.

I've never liked how Microsoft made the ROUND function though, up to you but since we're customizing it. I always have thought that the second parameter should be a power of 10. So -2 would be 10^-2 or the 100ths place. 100s would be 10^2 If you want it that way:

This would round to 100s, If e were -2 it would round to 100ths.

Sub RoundNumbers()
Dim c As Range, e As Long
e = 2
For Each c In Selection
    If IsNumeric(c) And c <> "" Then c = Application.WorksheetFunction.Round(c, -e)
Next
End Sub