I want to make a macro where you just input 100m and the dat
automatically converts to 100,000,000, etc. Anyone has some leads
Examples

--
jomn
-----------------------------------------------------------------------
jomni's Profile: http://www.excelforum.com/member.php...nfo&userid=774
View this thread: http://www.excelforum.com/showthread.php?threadid=37923

Enter the following code in the sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

If UCase(Right(Target.Value, 1)) = "M" Then
Target.Value = Left(Target.Value, Len(Target.Value) - 1) *
1000000
ElseIf UCase(Right(Target.Value, 1)) = "K" Then
Target.Value = Left(Target.Value, Len(Target.Value) - 1) *
1000
End If

End Sub


Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=379235

"jomni" wrote
in message ...

I want to make a macro where you just input 100m and the data
automatically converts to 100,000,000, etc. Anyone has some leads?
Examples?


Hi Jomni,

Shouldn't be too difficult.

I would suggest you use a worksheet_change event to trigger a check on
the taget cell (or cells) and pattern match where the entry is a text
string of
the form "[N]m" where [N] is one or more numeric digits.

HTH,

Alan.

Dear Jomni.
The maximum characters # is 15 and it accurate; example, if you inter
123,123,123,123,123,123 thats 18 characters, excels will save it as 15
character only as: 123,123,123,123,123,000.But that rarely happen.

"jomni" wrote:


I want to make a macro where you just input 100m and the data
automatically converts to 100,000,000, etc. Anyone has some leads?
Examples?


--
jomni
------------------------------------------------------------------------
jomni's Profile: http://www.excelforum.com/member.php...fo&userid=7744
View this thread: http://www.excelforum.com/showthread...hreadid=379235

"mangesh_yadav"
wrote in
message
news:mangesh_yadav.1qnlyd_1118815523.7323@excelfor um-nospam.com...

Enter the following code in the sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

If UCase(Right(Target.Value, 1)) = "M" Then
Target.Value = Left(Target.Value, Len(Target.Value) - 1) *
1000000
ElseIf UCase(Right(Target.Value, 1)) = "K" Then
Target.Value = Left(Target.Value, Len(Target.Value) - 1) *
1000
End If

End Sub


Mangesh


--
mangesh_yadav
--------------------------------------------------------------------
----
mangesh_yadav's Profile:
http://www.excelforum.com/member.php...o&userid=10470
View this thread:
http://www.excelforum.com/showthread...hreadid=379235


Very cool!

I would consider putting an error check in there, else if you enter,
say, "Mum" in a cell it will error out.

However, still very cool!

Alan.

Hi Alan,

That depends on the OP (whether to have any validations or not), but
yes, it would give an error in that case for sure. Thanks.

Mangesh



I would consider putting an error check in there, else if you enter,
say, "Mum" in a cell it will error out.

However, still very cool!

Alan.


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=379235