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

Hi tlee,

Try:
=MOD(A1*10,10)
copied down as far as needed

--
Cheers
macropod
[Microsoft MVP - Word]


"tlee" <(E-Mail Removed)> wrote in message news:%(E-Mail Removed)...
> 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
>

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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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

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" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message news:%(E-Mail Removed)...
>>> 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
>

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" <(E-Mail Removed)> wrote in message
> news:#(E-Mail Removed)...
>> 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
>

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" <(E-Mail Removed)> wrote in message
news:%23p%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:#(E-Mail Removed)...
>>> 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
>>
>
>

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" <(E-Mail Removed)> wrote in message
> news:%23p%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>>> news:#(E-Mail Removed)...
>>>> 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
>>>
>>
>>
>
>