How to check the decimal point of floating number using macro???

[Guest]

Hi,

I would like to create a macro that can help me to compare the 1st decimal
point of floating numbers in a data list; then round up the number based on
the condition below:-

- if the 1st decimal point of the number(s) is .3, for example 2.3 / 8.3 /
7.3; then the numbers will be rounded to 3 / 9 / 8 respectively.
-if the 1st decimal point of the number(s) is other than .3, for example 4.1
/ 5.8 / 9.3; then the numbers will be rounded to 4 / 5 / 9 respectively.

I can get the macro done with If...Then... Else control structure but I
can't get the macro to specifically check the number(s) using the 1st decimal
point.

So, anyone has any ideal how to solve this problem???
Advise is needed over here.....

Thanking in advanced.

 

[Guest]

Try:

Sub jacaround()
Dim n As Integer
v = Selection.Value
t = Selection.Text
If InStr(t, ".") = 0 Then
Exit Sub
End If
s = Split(t, ".")
If s(1) = "" Then
Exit Sub
End If
n = Left(s(1), 1)
If n = 3 Then
Selection.Value = Fix(v) + 1
Else
Selection.Value = Fix(v)
End If
End Sub

 

[Guest]

Thanks for your help....
will try out the code!

: )

 

[Guest]

Update this post if problems arise

 

[Ron Rosenfeld]

Hi,

I would like to create a macro that can help me to compare the 1st decimal
point of floating numbers in a data list; then round up the number based on
the condition below:-

- if the 1st decimal point of the number(s) is .3, for example 2.3 / 8.3 /
7.3; then the numbers will be rounded to 3 / 9 / 8 respectively.
-if the 1st decimal point of the number(s) is other than .3, for example 4.1
/ 5.8 / 9.3; then the numbers will be rounded to 4 / 5 / 9 respectively.

I can get the macro done with If...Then... Else control structure but I
can't get the macro to specifically check the number(s) using the 1st decimal
point.

So, anyone has any ideal how to solve this problem???
Advise is needed over here.....

Thanking in advanced.

Function RndPt3UP(rg As Range) As Double
Dim lTemp As Long
Dim lDec As Long

lTemp = Int(rg.Value * 10)

Select Case Abs(lTemp Mod 10)
Case 3
RndPt3UP = Application.WorksheetFunction.RoundUp(rg, 0)
Case Else
RndPt3UP = Application.WorksheetFunction.RoundDown(rg, 0)
End Select

End Function

The routine will handle both positive and negative numbers. Depending on how
you want to handle negative numbers, you may want to do some modifications.

--ron