100M = 100,000,000; 100k = 100,000
 

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

100M = 100,000,000; 100k = 100,000
 

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

100M = 100,000,000; 100k = 100,000
 

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

100M = 100,000,000; 100k = 100,000
 

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

100M = 100,000,000; 100k = 100,000
 

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

100M = 100,000,000; 100k = 100,000
 

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