Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I Round up to the nearest .25 | Excel Discussion (Misc queries) | |||
Formula for rounding number to nearest hundred? | Excel Discussion (Misc queries) | |||
Formula for rounding number to nearest hundred? | Excel Discussion (Misc queries) | |||
rounding to nearest hundred dollar in Excel | New Users to Excel | |||
How do I format cells to round to the nearest thousands without .. | Excel Discussion (Misc queries) |