Macro to round highlighted cells to nearest hundred
 

I'm a beginner at macro stuff and I had a quick question.
Let's say A1 = 225, A2 = 189, A3 = 1334
Is there a way to set up a macro is if I hightlight A1:A3 and then run
the macro the cells will automatically change to nearest hundreds?
So after I run the macro it will be
A1 = 200, A2 = 200, A3 = 1300

I currently tried recording a macro where I had the values in A1:A3 and
then in B1 I put =if(a10, round(a1,-2),0)
but there's a few problems with this: it limits to positive numbers
only, and for it to work I need to highlight/put the cursor on B1 first
and then run the macro, also the values *need* to be in the A column.

I'm assuming there's an easier way to do this, can anyone help out?
thanks!


--
zaplutus
------------------------------------------------------------------------
zaplutus's Profile: http://www.excelforum.com/member.php...o&userid=32084
View this thread: http://www.excelforum.com/showthread...hreadid=518383

Macro to round highlighted cells to nearest hundred
 

Sub Round100()
Dim cell As Range
For Each cell In Selection
If IsNumeric(cell) Then
cell.Value = Application.Round(cell.Value, -2)
End If
Next cell
End Sub


You could do it with a formula

=ROUND(A1,-2)

and copy down

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"zaplutus" wrote in
message ...

I'm a beginner at macro stuff and I had a quick question.
Let's say A1 = 225, A2 = 189, A3 = 1334
Is there a way to set up a macro is if I hightlight A1:A3 and then run
the macro the cells will automatically change to nearest hundreds?
So after I run the macro it will be
A1 = 200, A2 = 200, A3 = 1300

I currently tried recording a macro where I had the values in A1:A3 and
then in B1 I put =if(a10, round(a1,-2),0)
but there's a few problems with this: it limits to positive numbers
only, and for it to work I need to highlight/put the cursor on B1 first
and then run the macro, also the values *need* to be in the A column.

I'm assuming there's an easier way to do this, can anyone help out?
thanks!


--
zaplutus
------------------------------------------------------------------------
zaplutus's Profile:
http://www.excelforum.com/member.php...o&userid=32084
View this thread: http://www.excelforum.com/showthread...hreadid=518383

Macro to round highlighted cells to nearest hundred
 

It works great, thanks!


--
zaplutus
------------------------------------------------------------------------
zaplutus's Profile: http://www.excelforum.com/member.php...o&userid=32084
View this thread: http://www.excelforum.com/showthread...hreadid=518383