rounding to .5 in VBA

B

Brad

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
 
V

Vasant Nanavati

WorksheetFunction.Round(2 * DteDiff, 0) / 2

if DteDiff is what you are looking to round to the nearest 0.5.
 
D

David

Hi,
I think this will do it for you:
DteDiff = Round((Range("$L$11") - DateValue(ans)) / 30.42,1)
Thanks,
 
J

JE McGimpsey

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top