Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to check the last digit of each cells vaue in the column
Hi all,
Could anyone know how to use Macro to check the last digit (1 to 9) of the cell value? 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 Thanks Tlee |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to check the last digit of each cells vaue in the column
Hi tlee,
Try: =MOD(A1*10,10) copied down as far as needed -- Cheers macropod [Microsoft MVP - Word] "tlee" wrote in message ... Hi all, Could anyone know how to use Macro to check the last digit (1 to 9) of the cell value? 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 Thanks Tlee |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to check the last digit of each cells vaue in the column
Hi Macropod,
Thanks for your message first. However, how do I change to Macro VBA? since I would like to let it check automatically. tlee Hi tlee, Try: =MOD(A1*10,10) copied down as far as needed -- Cheers macropod [Microsoft MVP - Word] "tlee" wrote in message ... Hi all, Could anyone know how to use Macro to check the last digit (1 to 9) of the cell value? 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 Thanks Tlee |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to check the last digit of each cells vaue in the column
dim cell as range
for each cell in Range("A1:A500") if right(cell.value,1)="9" Then cell.Value = cell.value * 10 end if next "tlee" wrote in message ... Hi all, Could anyone know how to use Macro to check the last digit (1 to 9) of the cell value? 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 Thanks Tlee |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to check the last digit of each cells vaue in the column
Hi tlee,
You can do it without a macro, by checking the 'Fixed Decimal' option (under Tools|Options|Edit) and setting its value to '-1'. Note: this will affect all values in all workbooks until you uncheck the 'Fixed Decimal' option or change its value to something else. For an automated macro approach, which can be applied to just the specified range, you could use something like the following macro attached to the relevant worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, ActiveSheet.Range("A1:A1000")) Is Nothing Then Exit Sub With Application .EnableEvents = False If IsNumeric(Target.Value) Then Target.Value = Target.Value * 10 .EnableEvents = True End With End Sub Change the range ("A1:A1000") to suit your needs. -- Cheers macropod [Microsoft MVP - Word] "tlee" wrote in message ... Hi Macropod, Thanks for your message first. However, how do I change to Macro VBA? since I would like to let it check automatically. tlee Hi tlee, Try: =MOD(A1*10,10) copied down as far as needed -- Cheers macropod [Microsoft MVP - Word] "tlee" wrote in message ... Hi all, Could anyone know how to use Macro to check the last digit (1 to 9) of the cell value? 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 Thanks Tlee |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to check the last digit of each cells vaue in the column
Hi Patrick,
Thanks for your message. And I rasie the other thread about check the decimal point digit. Could you help ? Thanks tlee dim cell as range for each cell in Range("A1:A500") if right(cell.value,1)="9" Then cell.Value = cell.value * 10 end if next "tlee" wrote in message ... Hi all, Could anyone know how to use Macro to check the last digit (1 to 9) of the cell value? 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 Thanks Tlee |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to check the last digit of each cells vaue in the column
Give this a try: Sub dk() x = Right(Range("A1").Value, InStr(Range("A1"), ".") + 1) MsgBox x End Sub Change Range("A1") to your actual range, or object variable for a range. "tlee" wrote in message ... Hi Patrick, Thanks for your message. And I rasie the other thread about check the decimal point digit. Could you help ? Thanks tlee dim cell as range for each cell in Range("A1:A500") if right(cell.value,1)="9" Then cell.Value = cell.value * 10 end if next "tlee" wrote in message ... Hi all, Could anyone know how to use Macro to check the last digit (1 to 9) of the cell value? 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 Thanks Tlee |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to check the last digit of each cells vaue in the column
Hi JLGWhiz,
Thanks for your help! tlee Give this a try: Sub dk() x = Right(Range("A1").Value, InStr(Range("A1"), ".") + 1) MsgBox x End Sub Change Range("A1") to your actual range, or object variable for a range. "tlee" wrote in message ... Hi Patrick, Thanks for your message. And I rasie the other thread about check the decimal point digit. Could you help ? Thanks tlee dim cell as range for each cell in Range("A1:A500") if right(cell.value,1)="9" Then cell.Value = cell.value * 10 end if next "tlee" wrote in message ... Hi all, Could anyone know how to use Macro to check the last digit (1 to 9) of the cell value? 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 Thanks Tlee |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to check the last digit of each cells vaue in the column
Hi JLGWhiz,
Thanks for your help! tlee Give this a try: Sub dk() x = Right(Range("A1").Value, InStr(Range("A1"), ".") + 1) MsgBox x End Sub Change Range("A1") to your actual range, or object variable for a range. "tlee" wrote in message ... Hi Patrick, Thanks for your message. And I rasie the other thread about check the decimal point digit. Could you help ? Thanks tlee dim cell as range for each cell in Range("A1:A500") if right(cell.value,1)="9" Then cell.Value = cell.value * 10 end if next "tlee" wrote in message ... Hi all, Could anyone know how to use Macro to check the last digit (1 to 9) of the cell value? 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 Thanks Tlee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to hide a vaue | New Users to Excel | |||
How can you format cells in a column to display a 50 digit binary | Excel Discussion (Misc queries) | |||
Can Formulas use a certain Digit, like the 1st or 2nd Digit, in all Cells in a Range? | Excel Programming | |||
Mod-10 Check Digit | Excel Worksheet Functions | |||
Mod-10 Check Digit | Excel Worksheet Functions |