# Macro to round values

#### RR Guy

##### New Member
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.

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Code:
``````Sub RoundNumbers()
Dim c As Range
For Each c In Selection
If IsNumeric(c) And c <> "" Then c = Int(c)
Next
End Sub``````

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

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

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,

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!

Code:
``````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?

Code:
``````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.
Code:
``````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``````

Thank you!! This is exactly what I needed.

Code:
``````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.
Code:
``````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``````

Replies
2
Views
130
Replies
3
Views
131
Replies
4
Views
259
Replies
0
Views
256
Replies
1
Views
91

### Forum statistics

1,203,103
Messages
6,053,539
Members
444,670
Latest member
laurenmjones1111 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back