How to check the decimal number of each cells vaue in the column

[tlee]

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

 

[Joel]

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]

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

 

[Joel]

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]

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.