Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Excel know 1M is 1,000,000
How can I make excel know that when I type 1M in a cell the number
1,000,000 should appear in that cell (2M will appear as 2,000,000 etc)? Thanks, Amir |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Excel know 1M is 1,000,000
Tools=Autocorrect options . . .
-- Regards, Tom Ogilvy "amirstal" wrote: How can I make excel know that when I type 1M in a cell the number 1,000,000 should appear in that cell (2M will appear as 2,000,000 etc)? Thanks, Amir |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Excel know 1M is 1,000,000
On Jun 20, 11:50 am, Tom Ogilvy
wrote: Tools=Autocorrect options . . . -- Regards, Tom Ogilvy "amirstal" wrote: How can I make excel know that when I type 1M in a cell the number 1,000,000 should appear in that cell (2M will appear as 2,000,000 etc)? Thanks, Amir Thanks. But is there a way to make a rule so I won't have to enter all manually. For example, excel should know that 1.2m means 1200000 and 3.873m is 3873000... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Excel know 1M is 1,000,000
If you want it to change in the cell, I believe you would need to write code
that would parse out the entry and do the appropriate multiplication using the change event. -- Regards, Tom Ogilvy "amirstal" wrote: On Jun 20, 11:50 am, Tom Ogilvy wrote: Tools=Autocorrect options . . . -- Regards, Tom Ogilvy "amirstal" wrote: How can I make excel know that when I type 1M in a cell the number 1,000,000 should appear in that cell (2M will appear as 2,000,000 etc)? Thanks, Amir Thanks. But is there a way to make a rule so I won't have to enter all manually. For example, excel should know that 1.2m means 1200000 and 3.873m is 3873000... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Excel know 1M is 1,000,000
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit Application.EnableEvents = False With Target If LCase(Right$(.Value2, 1)) = "m" And _ IsNumeric(Left$(.Value2, Len(.Value2) - 1)) Then .Value = Left$(.Value2, Len(.Value2) - 1) * 1000000 End If End With ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "amirstal" wrote in message oups.com... On Jun 20, 11:50 am, Tom Ogilvy wrote: Tools=Autocorrect options . . . -- Regards, Tom Ogilvy "amirstal" wrote: How can I make excel know that when I type 1M in a cell the number 1,000,000 should appear in that cell (2M will appear as 2,000,000 etc)? Thanks, Amir Thanks. But is there a way to make a rule so I won't have to enter all manually. For example, excel should know that 1.2m means 1200000 and 3.873m is 3873000... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Excel know 1M is 1,000,000
On Jun 20, 12:28 pm, "Bob Phillips" wrote:
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit Application.EnableEvents = False With Target If LCase(Right$(.Value2, 1)) = "m" And _ IsNumeric(Left$(.Value2, Len(.Value2) - 1)) Then .Value = Left$(.Value2, Len(.Value2) - 1) * 1000000 End If End With ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "amirstal" wrote in message oups.com... On Jun 20, 11:50 am, Tom Ogilvy wrote: Tools=Autocorrect options . . . -- Regards, Tom Ogilvy "amirstal" wrote: How can I make excel know that when I type 1M in a cell the number 1,000,000 should appear in that cell (2M will appear as 2,000,000 etc)? Thanks, Amir Thanks. But is there a way to make a rule so I won't have to enter all manually. For example, excel should know that 1.2m means 1200000 and 3.873m is 3873000... This is great. Thanks very much. However, when I try to use with the negative sign, it does not work for example: if I type -0.3m in order to get -300000, I am getting an error message saying "The formula you typed contains an error." |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Excel know 1M is 1,000,000
****if**** you don't need the number for any subsequent formulas, then
you can set a custom format - you wouldn't need the "m", just type 3 & it would give you 3,000,000 but the problem is that the entry stays "3", not "3,000,000", so for use in formulas it doesn't work. (i tried this with adding thousands.......) (i can't find the reference right this minute - i'm still looking - post back if you want it - assuming i can find it!) susan On Jun 20, 2:03 pm, amirstal wrote: On Jun 20, 12:28 pm, "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit Application.EnableEvents = False With Target If LCase(Right$(.Value2, 1)) = "m" And _ IsNumeric(Left$(.Value2, Len(.Value2) - 1)) Then .Value = Left$(.Value2, Len(.Value2) - 1) * 1000000 End If End With ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "amirstal" wrote in message roups.com... On Jun 20, 11:50 am, Tom Ogilvy wrote: Tools=Autocorrect options . . . -- Regards, Tom Ogilvy "amirstal" wrote: How can I make excel know that when I type 1M in a cell the number 1,000,000 should appear in that cell (2M will appear as 2,000,000 etc)? Thanks, Amir Thanks. But is there a way to make a rule so I won't have to enter all manually. For example, excel should know that 1.2m means 1200000 and 3.873m is 3873000... This is great. Thanks very much. However, when I try to use with the negative sign, it does not work for example: if I type -0.3m in order to get -300000, I am getting an error message saying "The formula you typed contains an error."- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Make table query will work in datasheet view but will not make tab | Excel Discussion (Misc queries) | |||
How do I make make comment format default? | Excel Discussion (Misc queries) | |||
make macros usable! make instructions in plain english !!!! | Excel Worksheet Functions | |||
Excel templates. Wanna make excel look like regular blue lined pap | Excel Discussion (Misc queries) | |||
how do I make make my hyperlinks show the email address they are . | Excel Discussion (Misc queries) |