someone-somewhere

asked on

# Excel Macro button to round a cell to the nearest 100?

Hi everyone:

Is there a way to write a macro such that it will replace the active cell with its nearest 100 --- the active cell can be either a formula or an actual value. Suppose the current cell is =A1+B1, then I wish to replace it with =round((A1+B1), -2). If the current value is 190192, then it will be replaced with 190200. The challenge is that there are many many cells that I need to convert, and I was wondering if I can create a button, and assign a macro to it, so that the macro can do this automatically.

I'm not familiar with VBA, but I suppose it'd be something like this (should be pretty straightforward in other languages but I have no clue about VBA)

(1) get active cell value or formula, store it to a temporary variable;

(2) update the formula or the value using the round() function;

(3) add the new formula or value back to the active cell, replacing the current formula or value.

Can someone help me with this? I'd really appreciate it... Thanks

Is there a way to write a macro such that it will replace the active cell with its nearest 100 --- the active cell can be either a formula or an actual value. Suppose the current cell is =A1+B1, then I wish to replace it with =round((A1+B1), -2). If the current value is 190192, then it will be replaced with 190200. The challenge is that there are many many cells that I need to convert, and I was wondering if I can create a button, and assign a macro to it, so that the macro can do this automatically.

I'm not familiar with VBA, but I suppose it'd be something like this (should be pretty straightforward in other languages but I have no clue about VBA)

(1) get active cell value or formula, store it to a temporary variable;

(2) update the formula or the value using the round() function;

(3) add the new formula or value back to the active cell, replacing the current formula or value.

Can someone help me with this? I'd really appreciate it... Thanks

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Try This

RoundIt.xlsm

RoundIt.xlsm

ASKER