I want to create a macro that will round a number with decimal places up and then delete all the numbers to the right of the decimal place and the decimal place it self. So I am hoping that it will take a cell that has 49938.523566 and turn it into 49939 but if it was 49938.4235666 it would turn it into 49938.
maybe this Sub test() Const whatColumn As String = "A" Const whatSheet As String = "Sheet1" Dim ws As Worksheet Dim curCell As Variant Dim lastrow As Long Dim iRound As Long Set ws = Worksheets(whatSheet) lastrow = ws.Range(whatColumn & Rows.Count).End(xlUp).Row For Each curCell In ws.Range("A1:A" & lastrow) iRound = Round(curCell.Value, 0) curCell.Value = iRound Next End Sub "aintlifegrand79" wrote: > I want to create a macro that will round a number with decimal places up and > then delete all the numbers to the right of the decimal place and the decimal > place it self. So I am hoping that it will take a cell that has > 49938.523566 and turn it into 49939 but if it was 49938.4235666 it would turn > it into 49938.
Give this a try... Sub RangeRounder() Dim C As Range For Each C In Selection C.Value = Format(C.Value, "0") Next End Sub It will round each entry in the selection the way you indicated you wanted. Note that VB has a Round function, but I did not use it because I'm guessing it doesn't work the way you would want. The Round function uses something known as "Banker's Rounding"; it will produce the same results as the Format function that I used EXCEPT when your number end in .5 (point-five)... Banker's Rounding rounds this to the nearest even integer so that 1.5 and 2.5 would both round to 2... the Format function uses "normal" rounding and would round 2.5 up to 3. Rick "aintlifegrand79" wrote in message news:E18CDCC5-0801-47D0-A32D-4CB9938E10DF@microsoft.com... >I want to create a macro that will round a number with decimal places up >and > then delete all the numbers to the right of the decimal place and the > decimal > place it self. So I am hoping that it will take a cell that has > 49938.523566 and turn it into 49939 but if it was 49938.4235666 it would > turn > it into 49938.
This worked perfectly thank you. Mike thank you as well for your help. "Rick Rothstein (MVP - VB)" wrote: > Give this a try... > > Sub RangeRounder() > Dim C As Range > For Each C In Selection > C.Value = Format(C.Value, "0") > Next > End Sub > > It will round each entry in the selection the way you indicated you wanted. > Note that VB has a Round function, but I did not use it because I'm guessing > it doesn't work the way you would want. The Round function uses something > known as "Banker's Rounding"; it will produce the same results as the Format > function that I used EXCEPT when your number end in .5 (point-five)... > Banker's Rounding rounds this to the nearest even integer so that 1.5 and > 2.5 would both round to 2... the Format function uses "normal" rounding and > would round 2.5 up to 3. > > Rick > > > "aintlifegrand79" wrote in > message news:E18CDCC5-0801-47D0-A32D-4CB9938E10DF@microsoft.com... > >I want to create a macro that will round a number with decimal places up > >and > > then delete all the numbers to the right of the decimal place and the > > decimal > > place it self. So I am hoping that it will take a cell that has > > 49938.523566 and turn it into 49939 but if it was 49938.4235666 it would > > turn > > it into 49938. > >
