|
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 04:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com