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

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

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

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

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