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

T

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
 
J

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

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
 
J

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

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.
 

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