Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to check the decimal point of floating number using macro???
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to check the decimal point of floating number using macro???
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 -- Gary''s Student - gsnu200722 "Jac" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to check the decimal point of floating number using macro?
Thanks for your help....
will try out the code! : ) "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200722 "Jac" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to check the decimal point of floating number using macro?
Update this post if problems arise
-- Gary''s Student - gsnu200722 "Jac" wrote: Thanks for your help.... will try out the code! : ) "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200722 "Jac" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to check the decimal point of floating number using macro???
On Wed, 16 May 2007 06:06:02 -0700, Jac wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
decimal point in general number | New Users to Excel | |||
starts with decimal point only if number is like 0.###... | Excel Worksheet Functions | |||
Check if there is a decimal point | Excel Programming | |||
Floating point number comparison | Excel Programming | |||
Converting 2-place decimal value to floating point decimal number with leading zero | Excel Discussion (Misc queries) |