Adding Round funciton to handful of numbers

"Rick Rothstein" wrote:
you can just use normal cell formatting.

That's what I thought, too; but I'm drawing a blank. What numeric format
displays the equivalent of ROUND(...,-2) -- i.e. rounds to hundreds?


Okay, the fact that you want to retain the original number and only show
the rounded value is different than I originally interpreted your
response.

Yeah, Jamie is feeding his/her requirements to us in pieces. See the
response to "p45cal". Specifically, Jamie writes: "I would also like to
make the macro so that I can highlight any cell either by hitting shift or
holding down ctrl and the macro will apply to all selected cells".

Sounds like Jamie is hoping for some kind of event macro. Any ideas?

I would opt for a "button" (control) that invokes the macro after making the
cell selection.

But then again, I do not trust my interpretation of Jamie's requirements.
Personally, I would still opt for a formatting solution, unless I am
crafting the worksheet for others to use.


----- original message -----

"Rick Rothstein" wrote in message
...
Okay, the fact that you want to retain the original number and only show
the rounded value is different than I originally interpreted your
response. I'm thinking, as long as I understand what you want correctly,
that you can just use normal cell formatting. Select all the cells you
want to round (whether they current have values in them or not... think of
the future possibilities), click Format/Cells in the menu bar, select the
Number tab on the dialog box that comes up, select "Number" from the
Category List and choose the number of decimal places you want all your
numbers rounded to, then click OK. When you go back to your sheet, any
numbers in those cells you selected originally will now display with the
number of decimal places you picked, but the actual value in the cells
will not be changed.

--
Rick (MVP - Excel)


"Jamie" wrote in message
...
I would like the have my number normally rounded. For example, 88,888
would
round to 88,900. As if I were to have the following formula:
=Round("88888",-2).


"Rick Rothstein" wrote:

I think you will need a macro to do this. How did you want your numbers
rounded (round up, round down, round to nearest interval, Banker's
Rounding,
normal rounding, to a set number of decimal places possibly coupled with
one
of the previous methods, some other way)?

--
Rick (MVP - Excel)


"Jamie" wrote in message
news Hi,

I have a sheet filled with raw data. I need to round each number and
was
wondering if there is a formula or macro I can run to accomplish this.
All
the numbers were entered in by hand.

Basically, I would like to highlight each number I need rounded and
then
run
the formula/macro.

Let me know if this is possible.

Thanks

Okay, I think I'm up with what she wants now. I missed the...

ROUND(88888,-2) == 88,900

example in the OP's response to p45cal and simply thought the -2 was a typo
in the OP's response to me.

To Jamie: I don't think you can do what you want with formatting or with a
macro. There is no format for showing a number in hundreds and a macro
cannot make one up. To show a number in the format you want, that number
*must* be modified (divided by 100) and that would break your other
condition to show the original number in the cell. Excel can show numbers in
thousands (and millions, billions) and still keep the original number in the
cell, but not when the rounding is not a multiple of 3.

--
Rick (MVP - Excel)


"JoeU2004" wrote in message
...
"Rick Rothstein" wrote:
you can just use normal cell formatting.

That's what I thought, too; but I'm drawing a blank. What numeric format
displays the equivalent of ROUND(...,-2) -- i.e. rounds to hundreds?


Okay, the fact that you want to retain the original number and only show
the rounded value is different than I originally interpreted your
response.

Yeah, Jamie is feeding his/her requirements to us in pieces. See the
response to "p45cal". Specifically, Jamie writes: "I would also like to
make the macro so that I can highlight any cell either by hitting shift or
holding down ctrl and the macro will apply to all selected cells".

Sounds like Jamie is hoping for some kind of event macro. Any ideas?

I would opt for a "button" (control) that invokes the macro after making
the cell selection.

But then again, I do not trust my interpretation of Jamie's requirements.
Personally, I would still opt for a formatting solution, unless I am
crafting the worksheet for others to use.


----- original message -----

"Rick Rothstein" wrote in message
...
Okay, the fact that you want to retain the original number and only show
the rounded value is different than I originally interpreted your
response. I'm thinking, as long as I understand what you want correctly,
that you can just use normal cell formatting. Select all the cells you
want to round (whether they current have values in them or not... think
of the future possibilities), click Format/Cells in the menu bar, select
the Number tab on the dialog box that comes up, select "Number" from the
Category List and choose the number of decimal places you want all your
numbers rounded to, then click OK. When you go back to your sheet, any
numbers in those cells you selected originally will now display with the
number of decimal places you picked, but the actual value in the cells
will not be changed.

--
Rick (MVP - Excel)


"Jamie" wrote in message
...
I would like the have my number normally rounded. For example, 88,888
would
round to 88,900. As if I were to have the following formula:
=Round("88888",-2).


"Rick Rothstein" wrote:

I think you will need a macro to do this. How did you want your numbers
rounded (round up, round down, round to nearest interval, Banker's
Rounding,
normal rounding, to a set number of decimal places possibly coupled
with one
of the previous methods, some other way)?

--
Rick (MVP - Excel)


"Jamie" wrote in message
news Hi,

I have a sheet filled with raw data. I need to round each number and
was
wondering if there is a formula or macro I can run to accomplish
this. All
the numbers were entered in by hand.

Basically, I would like to highlight each number I need rounded and
then
run
the formula/macro.

Let me know if this is possible.

Thanks

Sorry for the confusion. Let me clarify myself further. I have a sheet with
hard coded numbers. There are certain numbers within the sheet that I would
like to round to the nearest hundreth (ex: 725 = 700, 888 = 900, 1,456 =
1,500 etc.).

I want to create a macro so that the original number in the formula bar
remains visible, however the number in the cell is rounded. Essentially, I
want a macro that take my formula bar number, 725, and inserts the round
function so that I now have a formula that looks like this: =Round("725",-2).
This will make the number that appears in the cell display the value of 700.

Earlier, JoeU2004 gave the macro below:

Sub doit()
Dim cell As Range
For Each cell In Selection
cell = WorksheetFunction.Round(cell, 2)
cell.NumberFormat = "0.00"
Next cell
End Sub

This worked, however it changed the number in the formula bar, which I do
not want to have happen. It also rounded to the wrong place, but I was able
to fix that since I at least know that much. If there is more clarification
please let me know.

Thanks,

Jamie

"JoeU2004" wrote:

"Rick Rothstein" wrote:
you can just use normal cell formatting.

That's what I thought, too; but I'm drawing a blank. What numeric format
displays the equivalent of ROUND(...,-2) -- i.e. rounds to hundreds?


Okay, the fact that you want to retain the original number and only show
the rounded value is different than I originally interpreted your
response.

Yeah, Jamie is feeding his/her requirements to us in pieces. See the
response to "p45cal". Specifically, Jamie writes: "I would also like to
make the macro so that I can highlight any cell either by hitting shift or
holding down ctrl and the macro will apply to all selected cells".

Sounds like Jamie is hoping for some kind of event macro. Any ideas?

I would opt for a "button" (control) that invokes the macro after making the
cell selection.

But then again, I do not trust my interpretation of Jamie's requirements.
Personally, I would still opt for a formatting solution, unless I am
crafting the worksheet for others to use.


----- original message -----

"Rick Rothstein" wrote in message
...
Okay, the fact that you want to retain the original number and only show
the rounded value is different than I originally interpreted your
response. I'm thinking, as long as I understand what you want correctly,
that you can just use normal cell formatting. Select all the cells you
want to round (whether they current have values in them or not... think of
the future possibilities), click Format/Cells in the menu bar, select the
Number tab on the dialog box that comes up, select "Number" from the
Category List and choose the number of decimal places you want all your
numbers rounded to, then click OK. When you go back to your sheet, any
numbers in those cells you selected originally will now display with the
number of decimal places you picked, but the actual value in the cells
will not be changed.

--
Rick (MVP - Excel)


"Jamie" wrote in message
...
I would like the have my number normally rounded. For example, 88,888
would
round to 88,900. As if I were to have the following formula:
=Round("88888",-2).


"Rick Rothstein" wrote:

I think you will need a macro to do this. How did you want your numbers
rounded (round up, round down, round to nearest interval, Banker's
Rounding,
normal rounding, to a set number of decimal places possibly coupled with
one
of the previous methods, some other way)?

--
Rick (MVP - Excel)


"Jamie" wrote in message
news Hi,

I have a sheet filled with raw data. I need to round each number and
was
wondering if there is a formula or macro I can run to accomplish this.
All
the numbers were entered in by hand.

Basically, I would like to highlight each number I need rounded and
then
run
the formula/macro.

Let me know if this is possible.

Thanks

Does this do what you want?

Sub RoundToHundreds()
Dim C As Range
For Each C In Selection
C.Formula = "=ROUND(" & C.Value & ", -2)"
Next
End Sub

--
Rick (MVP - Excel)


"Jamie" wrote in message
...
Sorry for the confusion. Let me clarify myself further. I have a sheet
with
hard coded numbers. There are certain numbers within the sheet that I
would
like to round to the nearest hundreth (ex: 725 = 700, 888 = 900, 1,456 =
1,500 etc.).

I want to create a macro so that the original number in the formula bar
remains visible, however the number in the cell is rounded. Essentially, I
want a macro that take my formula bar number, 725, and inserts the round
function so that I now have a formula that looks like this:
=Round("725",-2).
This will make the number that appears in the cell display the value of
700.

Earlier, JoeU2004 gave the macro below:

Sub doit()
Dim cell As Range
For Each cell In Selection
cell = WorksheetFunction.Round(cell, 2)
cell.NumberFormat = "0.00"
Next cell
End Sub

This worked, however it changed the number in the formula bar, which I do
not want to have happen. It also rounded to the wrong place, but I was
able
to fix that since I at least know that much. If there is more
clarification
please let me know.

Thanks,

Jamie

"JoeU2004" wrote:

"Rick Rothstein" wrote:
you can just use normal cell formatting.

That's what I thought, too; but I'm drawing a blank. What numeric format
displays the equivalent of ROUND(...,-2) -- i.e. rounds to hundreds?


Okay, the fact that you want to retain the original number and only
show
the rounded value is different than I originally interpreted your
response.

Yeah, Jamie is feeding his/her requirements to us in pieces. See the
response to "p45cal". Specifically, Jamie writes: "I would also like to
make the macro so that I can highlight any cell either by hitting shift
or
holding down ctrl and the macro will apply to all selected cells".

Sounds like Jamie is hoping for some kind of event macro. Any ideas?

I would opt for a "button" (control) that invokes the macro after making
the
cell selection.

But then again, I do not trust my interpretation of Jamie's requirements.
Personally, I would still opt for a formatting solution, unless I am
crafting the worksheet for others to use.


----- original message -----

"Rick Rothstein" wrote in message
...
Okay, the fact that you want to retain the original number and only
show
the rounded value is different than I originally interpreted your
response. I'm thinking, as long as I understand what you want
correctly,
that you can just use normal cell formatting. Select all the cells you
want to round (whether they current have values in them or not... think
of
the future possibilities), click Format/Cells in the menu bar, select
the
Number tab on the dialog box that comes up, select "Number" from the
Category List and choose the number of decimal places you want all your
numbers rounded to, then click OK. When you go back to your sheet, any
numbers in those cells you selected originally will now display with
the
number of decimal places you picked, but the actual value in the cells
will not be changed.

--
Rick (MVP - Excel)


"Jamie" wrote in message
...
I would like the have my number normally rounded. For example, 88,888
would
round to 88,900. As if I were to have the following formula:
=Round("88888",-2).


"Rick Rothstein" wrote:

I think you will need a macro to do this. How did you want your
numbers
rounded (round up, round down, round to nearest interval, Banker's
Rounding,
normal rounding, to a set number of decimal places possibly coupled
with
one
of the previous methods, some other way)?

--
Rick (MVP - Excel)


"Jamie" wrote in message
news Hi,

I have a sheet filled with raw data. I need to round each number
and
was
wondering if there is a formula or macro I can run to accomplish
this.
All
the numbers were entered in by hand.

Basically, I would like to highlight each number I need rounded and
then
run
the formula/macro.

Let me know if this is possible.

Thanks

Take a look at the below Macro someone wrote for me a little while back:

Sub addround()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula And IsNumeric(cell) Then
cell = "=ROUND(" & Mid(cell.Formula, 2, 1000) & ",-2)"
End If
Next cell
End Sub

This macro does exactly what I need but enters the =Round() formula around a
formula I already have in the cell. For example if I had the formula =A25*G55
in a cell the above macro would change that cell formula to
=Round(A25*G55,-2).

Does that help anyone come up with macro so solve the problem of this post?

"Rick Rothstein" wrote:

Okay, I think I'm up with what she wants now. I missed the...

ROUND(88888,-2) == 88,900

example in the OP's response to p45cal and simply thought the -2 was a typo
in the OP's response to me.

To Jamie: I don't think you can do what you want with formatting or with a
macro. There is no format for showing a number in hundreds and a macro
cannot make one up. To show a number in the format you want, that number
*must* be modified (divided by 100) and that would break your other
condition to show the original number in the cell. Excel can show numbers in
thousands (and millions, billions) and still keep the original number in the
cell, but not when the rounding is not a multiple of 3.

--
Rick (MVP - Excel)


"JoeU2004" wrote in message
...
"Rick Rothstein" wrote:
you can just use normal cell formatting.

That's what I thought, too; but I'm drawing a blank. What numeric format
displays the equivalent of ROUND(...,-2) -- i.e. rounds to hundreds?


Okay, the fact that you want to retain the original number and only show
the rounded value is different than I originally interpreted your
response.

Yeah, Jamie is feeding his/her requirements to us in pieces. See the
response to "p45cal". Specifically, Jamie writes: "I would also like to
make the macro so that I can highlight any cell either by hitting shift or
holding down ctrl and the macro will apply to all selected cells".

Sounds like Jamie is hoping for some kind of event macro. Any ideas?

I would opt for a "button" (control) that invokes the macro after making
the cell selection.

But then again, I do not trust my interpretation of Jamie's requirements.
Personally, I would still opt for a formatting solution, unless I am
crafting the worksheet for others to use.


----- original message -----

"Rick Rothstein" wrote in message
...
Okay, the fact that you want to retain the original number and only show
the rounded value is different than I originally interpreted your
response. I'm thinking, as long as I understand what you want correctly,
that you can just use normal cell formatting. Select all the cells you
want to round (whether they current have values in them or not... think
of the future possibilities), click Format/Cells in the menu bar, select
the Number tab on the dialog box that comes up, select "Number" from the
Category List and choose the number of decimal places you want all your
numbers rounded to, then click OK. When you go back to your sheet, any
numbers in those cells you selected originally will now display with the
number of decimal places you picked, but the actual value in the cells
will not be changed.

--
Rick (MVP - Excel)


"Jamie" wrote in message
...
I would like the have my number normally rounded. For example, 88,888
would
round to 88,900. As if I were to have the following formula:
=Round("88888",-2).


"Rick Rothstein" wrote:

I think you will need a macro to do this. How did you want your numbers
rounded (round up, round down, round to nearest interval, Banker's
Rounding,
normal rounding, to a set number of decimal places possibly coupled
with one
of the previous methods, some other way)?

--
Rick (MVP - Excel)


"Jamie" wrote in message
news Hi,

I have a sheet filled with raw data. I need to round each number and
was
wondering if there is a formula or macro I can run to accomplish
this. All
the numbers were entered in by hand.

Basically, I would like to highlight each number I need rounded and
then
run
the formula/macro.

Let me know if this is possible.

Thanks

That's it. Thanks. I'm sure I made that a lot harder than it needed to be.

"Jamie" wrote:

This works but there are two changes I would like to make to it.

Can I have it so that the original number remains in the "formula bar" but
the rounded number shows up in the cell? I would like to have it so that if
the number were 88,888 the macro would insert the formula =Round("88,888", -2)

Also, can the macro keep the same number formatting? The one you gave me
changes the formatting to general.

Thanks

"JoeU2004" wrote:

"Jamie" wrote:
Basically, I would like to highlight each number I need
rounded and then run the formula/macro.

If you want to use a macro, the following should suffice.


Sub doit()
Dim cell As Range
For Each cell In Selection
cell = WorksheetFunction.Round(cell, 2)
cell.NumberFormat = "0.00"
Next cell
End Sub


Some comments:

1. I added a line to change the format (cell.NumberFormat). Delete that if
you wish to retain the original format.

2. I use Worksheet.Function.Round instead of the VBA Round function because
the latter rounds different (so-called "banker's rounding"). For example,
if the cell value is 295.425, WorksheetFunction.Round(cell,2) results in
295.43, whereas Round(cell,2) results in 295.42.

3. If you are unfamiliar with using macros, do the following:

a. In an active worksheet, press alt+F11 to open the VB window.

b. In the VB window, click on Insert Module. That should open the VB
Editor pane.

c. Copy and paste the macro text above into the VB Editor pane.

d. In the worksheet window, select the cells to be converted, either one
at a time or use ctrl+leftClick. Then press alt+F8, select the macro, and
click Run.


"Jamie" wrote in message
news
Hi,

I have a sheet filled with raw data. I need to round each number and was
wondering if there is a formula or macro I can run to accomplish this. All
the numbers were entered in by hand.

Basically, I would like to highlight each number I need rounded and then
run
the formula/macro.

Let me know if this is possible.

Thanks

Are you *sure* that JoeU2004's macro is *really* it? As far as I can tell,
his code physically changes the original value in the cell to the rounded
value (thereby losing your original value).

--
Rick (MVP - Excel)


"Jamie" wrote in message
...
That's it. Thanks. I'm sure I made that a lot harder than it needed to be.

"Jamie" wrote:

This works but there are two changes I would like to make to it.

Can I have it so that the original number remains in the "formula bar"
but
the rounded number shows up in the cell? I would like to have it so that
if
the number were 88,888 the macro would insert the formula
=Round("88,888", -2)

Also, can the macro keep the same number formatting? The one you gave me
changes the formatting to general.

Thanks

"JoeU2004" wrote:

"Jamie" wrote:
Basically, I would like to highlight each number I need
rounded and then run the formula/macro.

If you want to use a macro, the following should suffice.


Sub doit()
Dim cell As Range
For Each cell In Selection
cell = WorksheetFunction.Round(cell, 2)
cell.NumberFormat = "0.00"
Next cell
End Sub


Some comments:

1. I added a line to change the format (cell.NumberFormat). Delete
that if
you wish to retain the original format.

2. I use Worksheet.Function.Round instead of the VBA Round function
because
the latter rounds different (so-called "banker's rounding"). For
example,
if the cell value is 295.425, WorksheetFunction.Round(cell,2) results
in
295.43, whereas Round(cell,2) results in 295.42.

3. If you are unfamiliar with using macros, do the following:

a. In an active worksheet, press alt+F11 to open the VB window.

b. In the VB window, click on Insert Module. That should open
the VB
Editor pane.

c. Copy and paste the macro text above into the VB Editor pane.

d. In the worksheet window, select the cells to be converted,
either one
at a time or use ctrl+leftClick. Then press alt+F8, select the macro,
and
click Run.


"Jamie" wrote in message
news Hi,

I have a sheet filled with raw data. I need to round each number and
was
wondering if there is a formula or macro I can run to accomplish
this. All
the numbers were entered in by hand.

Basically, I would like to highlight each number I need rounded and
then
run
the formula/macro.

Let me know if this is possible.

Thanks

This macro does exactly what I need but enters the =Round() formula around
a
formula I already have in the cell. For example if I had the formula
=A25*G55
in a cell the above macro would change that cell formula to
=Round(A25*G55,-2).

Okay, I'm confused again. Your original post said your cells had "raw data"
which I did not take to mean there were formulas in the cell. Now you are
mentioning formulas whereas each of your previous postings showed constant
values. And you say the code you just posted does what you want, but
obviously it doesn't (your example does not clarify what you are actually
looking for) or you wouldn't have posted in the first place. Instead of
adding new information with each post, do the following... show us a sample
of what is in a cell now... that is, what is in the Formula Bar and what is
displayed in the cell, then show us exactly what you want to be showing in
the Formula Bar and cell *after* the macro has run.

--
Rick (MVP - Excel)


"Jamie" wrote in message
...
Take a look at the below Macro someone wrote for me a little while back:

Sub addround()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula And IsNumeric(cell) Then
cell = "=ROUND(" & Mid(cell.Formula, 2, 1000) & ",-2)"
End If
Next cell
End Sub

This macro does exactly what I need but enters the =Round() formula around
a
formula I already have in the cell. For example if I had the formula
=A25*G55
in a cell the above macro would change that cell formula to
=Round(A25*G55,-2).

Does that help anyone come up with macro so solve the problem of this
post?

"Rick Rothstein" wrote:

Okay, I think I'm up with what she wants now. I missed the...

ROUND(88888,-2) == 88,900

example in the OP's response to p45cal and simply thought the -2 was a
typo
in the OP's response to me.

To Jamie: I don't think you can do what you want with formatting or with
a
macro. There is no format for showing a number in hundreds and a macro
cannot make one up. To show a number in the format you want, that number
*must* be modified (divided by 100) and that would break your other
condition to show the original number in the cell. Excel can show numbers
in
thousands (and millions, billions) and still keep the original number in
the
cell, but not when the rounding is not a multiple of 3.

--
Rick (MVP - Excel)


"JoeU2004" wrote in message
...
"Rick Rothstein" wrote:
you can just use normal cell formatting.

That's what I thought, too; but I'm drawing a blank. What numeric
format
displays the equivalent of ROUND(...,-2) -- i.e. rounds to hundreds?


Okay, the fact that you want to retain the original number and only
show
the rounded value is different than I originally interpreted your
response.

Yeah, Jamie is feeding his/her requirements to us in pieces. See the
response to "p45cal". Specifically, Jamie writes: "I would also like
to
make the macro so that I can highlight any cell either by hitting shift
or
holding down ctrl and the macro will apply to all selected cells".

Sounds like Jamie is hoping for some kind of event macro. Any ideas?

I would opt for a "button" (control) that invokes the macro after
making
the cell selection.

But then again, I do not trust my interpretation of Jamie's
requirements.
Personally, I would still opt for a formatting solution, unless I am
crafting the worksheet for others to use.


----- original message -----

"Rick Rothstein" wrote in message
...
Okay, the fact that you want to retain the original number and only
show
the rounded value is different than I originally interpreted your
response. I'm thinking, as long as I understand what you want
correctly,
that you can just use normal cell formatting. Select all the cells you
want to round (whether they current have values in them or not...
think
of the future possibilities), click Format/Cells in the menu bar,
select
the Number tab on the dialog box that comes up, select "Number" from
the
Category List and choose the number of decimal places you want all
your
numbers rounded to, then click OK. When you go back to your sheet, any
numbers in those cells you selected originally will now display with
the
number of decimal places you picked, but the actual value in the cells
will not be changed.

--
Rick (MVP - Excel)


"Jamie" wrote in message
...
I would like the have my number normally rounded. For example, 88,888
would
round to 88,900. As if I were to have the following formula:
=Round("88888",-2).


"Rick Rothstein" wrote:

I think you will need a macro to do this. How did you want your
numbers
rounded (round up, round down, round to nearest interval, Banker's
Rounding,
normal rounding, to a set number of decimal places possibly coupled
with one
of the previous methods, some other way)?

--
Rick (MVP - Excel)


"Jamie" wrote in message
news Hi,

I have a sheet filled with raw data. I need to round each number
and
was
wondering if there is a formula or macro I can run to accomplish
this. All
the numbers were entered in by hand.

Basically, I would like to highlight each number I need rounded
and
then
run
the formula/macro.

Let me know if this is possible.

Thanks

"Rick Rothstein" wrote:
Are you *sure* that JoeU2004's macro is *really* it?

I concur. In the final analysis, following Jamie's "twisty maze" of
ever-changing requirements, it seems that what does the job is a macro that
replaces the number in a cell with a formula of the form =ROUND(number,-2).
You provided that macro elsewhere in this "thread". ("Web" would be a
better description).


----- original message -----

"Rick Rothstein" wrote in message
...
Are you *sure* that JoeU2004's macro is *really* it? As far as I can tell,
his code physically changes the original value in the cell to the rounded
value (thereby losing your original value).

--
Rick (MVP - Excel)


"Jamie" wrote in message
...
That's it. Thanks. I'm sure I made that a lot harder than it needed to
be.

"Jamie" wrote:

This works but there are two changes I would like to make to it.

Can I have it so that the original number remains in the "formula bar"
but
the rounded number shows up in the cell? I would like to have it so that
if
the number were 88,888 the macro would insert the formula
=Round("88,888", -2)

Also, can the macro keep the same number formatting? The one you gave me
changes the formatting to general.

Thanks

"JoeU2004" wrote:

"Jamie" wrote:
Basically, I would like to highlight each number I need
rounded and then run the formula/macro.

If you want to use a macro, the following should suffice.


Sub doit()
Dim cell As Range
For Each cell In Selection
cell = WorksheetFunction.Round(cell, 2)
cell.NumberFormat = "0.00"
Next cell
End Sub


Some comments:

1. I added a line to change the format (cell.NumberFormat). Delete
that if
you wish to retain the original format.

2. I use Worksheet.Function.Round instead of the VBA Round function
because
the latter rounds different (so-called "banker's rounding"). For
example,
if the cell value is 295.425, WorksheetFunction.Round(cell,2) results
in
295.43, whereas Round(cell,2) results in 295.42.

3. If you are unfamiliar with using macros, do the following:

a. In an active worksheet, press alt+F11 to open the VB window.

b. In the VB window, click on Insert Module. That should open
the VB
Editor pane.

c. Copy and paste the macro text above into the VB Editor pane.

d. In the worksheet window, select the cells to be converted,
either one
at a time or use ctrl+leftClick. Then press alt+F8, select the macro,
and
click Run.


"Jamie" wrote in message
news Hi,

I have a sheet filled with raw data. I need to round each number and
was
wondering if there is a formula or macro I can run to accomplish
this. All
the numbers were entered in by hand.

Basically, I would like to highlight each number I need rounded and
then
run
the formula/macro.

Let me know if this is possible.

Thanks

There is no question I am confused as to what Jamie is actually looking for.
The only problem I know see with either of our macros is Jamie's latest
mention of (if I interpreted the post correctly) preserving formulas in
cells.

--
Rick (MVP - Excel)


"JoeU2004" wrote in message
...
"Rick Rothstein" wrote:
Are you *sure* that JoeU2004's macro is *really* it?

I concur. In the final analysis, following Jamie's "twisty maze" of
ever-changing requirements, it seems that what does the job is a macro
that replaces the number in a cell with a formula of the form
=ROUND(number,-2). You provided that macro elsewhere in this "thread".
("Web" would be a better description).


----- original message -----

"Rick Rothstein" wrote in message
...
Are you *sure* that JoeU2004's macro is *really* it? As far as I can
tell, his code physically changes the original value in the cell to the
rounded value (thereby losing your original value).

--
Rick (MVP - Excel)


"Jamie" wrote in message
...
That's it. Thanks. I'm sure I made that a lot harder than it needed to
be.

"Jamie" wrote:

This works but there are two changes I would like to make to it.

Can I have it so that the original number remains in the "formula bar"
but
the rounded number shows up in the cell? I would like to have it so
that if
the number were 88,888 the macro would insert the formula
=Round("88,888", -2)

Also, can the macro keep the same number formatting? The one you gave
me
changes the formatting to general.

Thanks

"JoeU2004" wrote:

"Jamie" wrote:
Basically, I would like to highlight each number I need
rounded and then run the formula/macro.

If you want to use a macro, the following should suffice.


Sub doit()
Dim cell As Range
For Each cell In Selection
cell = WorksheetFunction.Round(cell, 2)
cell.NumberFormat = "0.00"
Next cell
End Sub


Some comments:

1. I added a line to change the format (cell.NumberFormat). Delete
that if
you wish to retain the original format.

2. I use Worksheet.Function.Round instead of the VBA Round function
because
the latter rounds different (so-called "banker's rounding"). For
example,
if the cell value is 295.425, WorksheetFunction.Round(cell,2) results
in
295.43, whereas Round(cell,2) results in 295.42.

3. If you are unfamiliar with using macros, do the following:

a. In an active worksheet, press alt+F11 to open the VB window.

b. In the VB window, click on Insert Module. That should open
the VB
Editor pane.

c. Copy and paste the macro text above into the VB Editor pane.

d. In the worksheet window, select the cells to be converted,
either one
at a time or use ctrl+leftClick. Then press alt+F8, select the
macro, and
click Run.


"Jamie" wrote in message
news Hi,

I have a sheet filled with raw data. I need to round each number
and was
wondering if there is a formula or macro I can run to accomplish
this. All
the numbers were entered in by hand.

Basically, I would like to highlight each number I need rounded and
then
run
the formula/macro.

Let me know if this is possible.

Thanks