| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to check the decimal number of each cells vaue in the column
Hi all,
As refer to my pervious post, I would like to use Macro VBA to check the last digit (1 to 9) of the cell value automatically? My code as below: (may be it is complicated coding) For Each Col In Range("A2:A9999") If Col.Value < "" Then If Col.Value 10 And Col.Value <= 9999 Then Ans = Col.Value Mod 10 If Ans < 0 Then Col.Value = Col.Value * 10 Else Col.Value = Col.Value End If End If End If Next Col e.g. Cell A1 = 21, 22 ........, 29 then it will be multiply by 10 Cell A2 = 201, 202, ........., 209 then it will be multiply by 10 However, how can I check with the data which is decimal number too, such that 3.9 , 33.9 ......................... convert to 390, 3390...............................? Thanks tlee |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to check the decimal number of each cells vaue in the column
right(COL.VALUE,1) = "9"
OR VAL(right(COL.VALUE,1)) = 9 Right returns a string so you either have to compare to a string or convert the string to a number. "tlee" wrote: Hi all, As refer to my pervious post, I would like to use Macro VBA to check the last digit (1 to 9) of the cell value automatically? My code as below: (may be it is complicated coding) For Each Col In Range("A2:A9999") If Col.Value < "" Then If Col.Value 10 And Col.Value <= 9999 Then Ans = Col.Value Mod 10 If Ans < 0 Then Col.Value = Col.Value * 10 Else Col.Value = Col.Value End If End If End If Next Col e.g. Cell A1 = 21, 22 ........, 29 then it will be multiply by 10 Cell A2 = 201, 202, ........., 209 then it will be multiply by 10 However, how can I check with the data which is decimal number too, such that 3.9 , 33.9 ......................... convert to 390, 3390...............................? Thanks tlee |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to check the decimal number of each cells vaue in the column
Hi Joel,
Thanks for your message. If I want to check the digit which is 2 decimal point such that X.X1 X.X2 .... .... .... X.X8 X.X9 Have any more smart way to implement? Thanks tlee right(COL.VALUE,1) = "9" OR VAL(right(COL.VALUE,1)) = 9 Right returns a string so you either have to compare to a string or convert the string to a number. "tlee" wrote: Hi all, As refer to my pervious post, I would like to use Macro VBA to check the last digit (1 to 9) of the cell value automatically? My code as below: (may be it is complicated coding) For Each Col In Range("A2:A9999") If Col.Value < "" Then If Col.Value 10 And Col.Value <= 9999 Then Ans = Col.Value Mod 10 If Ans < 0 Then Col.Value = Col.Value * 10 Else Col.Value = Col.Value End If End If End If Next Col e.g. Cell A1 = 21, 22 ........, 29 then it will be multiply by 10 Cell A2 = 201, 202, ........., 209 then it will be multiply by 10 However, how can I check with the data which is decimal number too, such that 3.9 , 33.9 ......................... convert to 390, 3390...............................? Thanks tlee |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to check the decimal number of each cells vaue in the colu
right with the parameter 1 will always check the right digit of the number.
You will have a problem using this method if you have numbers which are 1 and 2 decimal points like right(COL.VALUE,1) = "1" 1.1 1.11 The numbers should be 1.10 1.11 Using format will correct for this problem right(format(COL.VALUE,"0.00"),1) = "1" the format statement will convert all the numbers to two decimal places which is the same as changing the format of the cells to numbers with 2 decimal places (worksheet menu format - cells - numbers). My code will match both number in the first example and only 1.11 in the 2nd example. "tlee" wrote: Hi Joel, Thanks for your message. If I want to check the digit which is 2 decimal point such that X.X1 X.X2 .... .... .... X.X8 X.X9 Have any more smart way to implement? Thanks tlee right(COL.VALUE,1) = "9" OR VAL(right(COL.VALUE,1)) = 9 Right returns a string so you either have to compare to a string or convert the string to a number. "tlee" wrote: Hi all, As refer to my pervious post, I would like to use Macro VBA to check the last digit (1 to 9) of the cell value automatically? My code as below: (may be it is complicated coding) For Each Col In Range("A2:A9999") If Col.Value < "" Then If Col.Value 10 And Col.Value <= 9999 Then Ans = Col.Value Mod 10 If Ans < 0 Then Col.Value = Col.Value * 10 Else Col.Value = Col.Value End If End If End If Next Col e.g. Cell A1 = 21, 22 ........, 29 then it will be multiply by 10 Cell A2 = 201, 202, ........., 209 then it will be multiply by 10 However, how can I check with the data which is decimal number too, such that 3.9 , 33.9 ......................... convert to 390, 3390...............................? Thanks tlee |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to check the decimal number of each cells vaue in the colu
Hi Joel,
Thanks for your help! Tlee right with the parameter 1 will always check the right digit of the number. You will have a problem using this method if you have numbers which are 1 and 2 decimal points like right(COL.VALUE,1) = "1" 1.1 1.11 The numbers should be 1.10 1.11 Using format will correct for this problem right(format(COL.VALUE,"0.00"),1) = "1" the format statement will convert all the numbers to two decimal places which is the same as changing the format of the cells to numbers with 2 decimal places (worksheet menu format - cells - numbers). My code will match both number in the first example and only 1.11 in the 2nd example. "tlee" wrote: Hi Joel, Thanks for your message. If I want to check the digit which is 2 decimal point such that X.X1 X.X2 .... .... .... X.X8 X.X9 Have any more smart way to implement? Thanks tlee right(COL.VALUE,1) = "9" OR VAL(right(COL.VALUE,1)) = 9 Right returns a string so you either have to compare to a string or convert the string to a number. "tlee" wrote: Hi all, As refer to my pervious post, I would like to use Macro VBA to check the last digit (1 to 9) of the cell value automatically? My code as below: (may be it is complicated coding) For Each Col In Range("A2:A9999") If Col.Value < "" Then If Col.Value 10 And Col.Value <= 9999 Then Ans = Col.Value Mod 10 If Ans < 0 Then Col.Value = Col.Value * 10 Else Col.Value = Col.Value End If End If End If Next Col e.g. Cell A1 = 21, 22 ........, 29 then it will be multiply by 10 Cell A2 = 201, 202, ........., 209 then it will be multiply by 10 However, how can I check with the data which is decimal number too, such that 3.9 , 33.9 ......................... convert to 390, 3390...............................? Thanks tlee |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to check the decimal number of each cells vaue in the colu
Hi Joel,
Thanks for your help! Tlee right with the parameter 1 will always check the right digit of the number. You will have a problem using this method if you have numbers which are 1 and 2 decimal points like right(COL.VALUE,1) = "1" 1.1 1.11 The numbers should be 1.10 1.11 Using format will correct for this problem right(format(COL.VALUE,"0.00"),1) = "1" the format statement will convert all the numbers to two decimal places which is the same as changing the format of the cells to numbers with 2 decimal places (worksheet menu format - cells - numbers). My code will match both number in the first example and only 1.11 in the 2nd example. "tlee" wrote: Hi Joel, Thanks for your message. If I want to check the digit which is 2 decimal point such that X.X1 X.X2 .... .... .... X.X8 X.X9 Have any more smart way to implement? Thanks tlee right(COL.VALUE,1) = "9" OR VAL(right(COL.VALUE,1)) = 9 Right returns a string so you either have to compare to a string or convert the string to a number. "tlee" wrote: Hi all, As refer to my pervious post, I would like to use Macro VBA to check the last digit (1 to 9) of the cell value automatically? My code as below: (may be it is complicated coding) For Each Col In Range("A2:A9999") If Col.Value < "" Then If Col.Value 10 And Col.Value <= 9999 Then Ans = Col.Value Mod 10 If Ans < 0 Then Col.Value = Col.Value * 10 Else Col.Value = Col.Value End If End If End If Next Col e.g. Cell A1 = 21, 22 ........, 29 then it will be multiply by 10 Cell A2 = 201, 202, ........., 209 then it will be multiply by 10 However, how can I check with the data which is decimal number too, such that 3.9 , 33.9 ......................... convert to 390, 3390...............................? Thanks tlee |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to check the last digit of each cells vaue in the column | Excel Programming | |||
| I need to check for a number in a column before continuing on | Excel Programming | |||
| How to check the decimal point of floating number using macro??? | Excel Programming | |||
| How can I check for a duplicate number in a column in Excel? | Excel Discussion (Misc queries) | |||
| Converting 2-place decimal value to floating point decimal number with leading zero | Excel Discussion (Misc queries) | |||
Hybrid Mode
