Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
rounding to .5 in VBA
I need help with rounding an answer to either a whole number or a .5 I know
this can be done in Excel and there are limitations with the Round() function in VBA. I've seen replies stating to use the application.round but how would I incorporate that into mine? Here is my code: Private Sub Worksheet_Change(ByVal Target As Range) ' This prorates the # of months' On Error GoTo ws_exit: Dim ans As String, DteDiff As Integer If Not Application.Intersect(Target, Range("$C$25:$C$54")) Is Nothing Then If UCase(Target.Value) = "Y" Then ' allows for Y and y With Worksheets("agreement") ans = InputBox("What is the warranty expiration date #", "Prorate months", _ Sheets("Agreement").Range("$A$1").Value) DteDiff = (Range("$L$11") - DateValue(ans)) / 30.42 Target = DteDiff End With End If |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
rounding to .5 in VBA
WorksheetFunction.Round(2 * DteDiff, 0) / 2
if DteDiff is what you are looking to round to the nearest 0.5. -- Vasant "Brad" wrote in message ... I need help with rounding an answer to either a whole number or a .5 I know this can be done in Excel and there are limitations with the Round() function in VBA. I've seen replies stating to use the application.round but how would I incorporate that into mine? Here is my code: Private Sub Worksheet_Change(ByVal Target As Range) ' This prorates the # of months' On Error GoTo ws_exit: Dim ans As String, DteDiff As Integer If Not Application.Intersect(Target, Range("$C$25:$C$54")) Is Nothing Then If UCase(Target.Value) = "Y" Then ' allows for Y and y With Worksheets("agreement") ans = InputBox("What is the warranty expiration date #", "Prorate months", _ Sheets("Agreement").Range("$A$1").Value) DteDiff = (Range("$L$11") - DateValue(ans)) / 30.42 Target = DteDiff End With End If |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
rounding to .5 in VBA
Hi,
I think this will do it for you: DteDiff = Round((Range("$L$11") - DateValue(ans)) / 30.42,1) Thanks, "Brad" wrote: I need help with rounding an answer to either a whole number or a .5 I know this can be done in Excel and there are limitations with the Round() function in VBA. I've seen replies stating to use the application.round but how would I incorporate that into mine? Here is my code: Private Sub Worksheet_Change(ByVal Target As Range) ' This prorates the # of months' On Error GoTo ws_exit: Dim ans As String, DteDiff As Integer If Not Application.Intersect(Target, Range("$C$25:$C$54")) Is Nothing Then If UCase(Target.Value) = "Y" Then ' allows for Y and y With Worksheets("agreement") ans = InputBox("What is the warranty expiration date #", "Prorate months", _ Sheets("Agreement").Range("$A$1").Value) DteDiff = (Range("$L$11") - DateValue(ans)) / 30.42 Target = DteDiff End With End If |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
rounding to .5 in VBA
Hello David,
Unfortunately that didn't work. Any other ideas? Thanks. "David" wrote: Hi, I think this will do it for you: DteDiff = Round((Range("$L$11") - DateValue(ans)) / 30.42,1) Thanks, "Brad" wrote: I need help with rounding an answer to either a whole number or a .5 I know this can be done in Excel and there are limitations with the Round() function in VBA. I've seen replies stating to use the application.round but how would I incorporate that into mine? Here is my code: Private Sub Worksheet_Change(ByVal Target As Range) ' This prorates the # of months' On Error GoTo ws_exit: Dim ans As String, DteDiff As Integer If Not Application.Intersect(Target, Range("$C$25:$C$54")) Is Nothing Then If UCase(Target.Value) = "Y" Then ' allows for Y and y With Worksheets("agreement") ans = InputBox("What is the warranty expiration date #", "Prorate months", _ Sheets("Agreement").Range("$A$1").Value) DteDiff = (Range("$L$11") - DateValue(ans)) / 30.42 Target = DteDiff End With End If |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
rounding to .5 in VBA
One way:
DteDiff = Round(2 * (Range("$L$11") - DateValue(ans)) / 30.42, 0) / 2 or, equivalently DteDiff = Round((Range("$L$11") - DateValue(ans)) / 15.21, 0) / 2 In article , "Brad" wrote: Hello David, Unfortunately that didn't work. Any other ideas? Thanks. "David" wrote: Hi, I think this will do it for you: DteDiff = Round((Range("$L$11") - DateValue(ans)) / 30.42,1) Thanks, "Brad" wrote: I need help with rounding an answer to either a whole number or a .5 I know this can be done in Excel and there are limitations with the Round() function in VBA. I've seen replies stating to use the application.round but how would I incorporate that into mine? Here is my code: Private Sub Worksheet_Change(ByVal Target As Range) ' This prorates the # of months' On Error GoTo ws_exit: Dim ans As String, DteDiff As Integer If Not Application.Intersect(Target, Range("$C$25:$C$54")) Is Nothing Then If UCase(Target.Value) = "Y" Then ' allows for Y and y With Worksheets("agreement") ans = InputBox("What is the warranty expiration date #", "Prorate months", _ Sheets("Agreement").Range("$A$1").Value) DteDiff = (Range("$L$11") - DateValue(ans)) / 30.42 Target = DteDiff End With End If |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ROUNDING FORMULA =ROUND(B12/$B$10,1) ROUNDING TO HIGH | Excel Worksheet Functions | |||
Rounding | Excel Discussion (Misc queries) | |||
I need a formula with rounding up & rounding down to the nearest . | Excel Worksheet Functions | |||
Rounding | Excel Worksheet Functions | |||
no rounding | Excel Worksheet Functions |