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

J

Jac

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.
 
G

Gary''s Student

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
 
R

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
 

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