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 - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Excel know 1M is 1,000,000
I think that this does it
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit Application.EnableEvents = False With Target If Right$(.Value2, 1) = "-" Then If LCase(Mid$(.Value2, Len(.Value2) - 1, 1)) = "m" And _ IsNumeric(Left$(.Value2, Len(.Value2) - 2)) Then .Value = "-" & Left$(.Value2, Len(.Value2) - 2) * 1000000 End If Else If LCase(Right$(.Value2, 1)) = "m" And _ IsNumeric(Left$(.Value2, Len(.Value2) - 1)) Then .Value = Left$(.Value2, Len(.Value2) - 1) * 1000000 End If End If End With ws_exit: Application.EnableEvents = True End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "amirstal" wrote in message ups.com... On Jun 20, 3:16 pm, "Bob Phillips" wrote: If you enter it like so 1M- (Excel doesn't like -1M), then you can use Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit Application.EnableEvents = False With Target If Right$(.Value2, 1) = "-" Then If LCase(Mid$(.Value2, Len(.Value2) - 1, 1)) = "m" And _ IsNumeric(Left$(.Value2, Len(.Value2) - 2)) Then .Value = "-" & Left$(.Value2, Len(.Value2) - 2) * 1000000 End If Else .Value = Left$(.Value2, Len(.Value2) - 1) * 1000000 End If End With ws_exit: Application.EnableEvents = True End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "amirstal" wrote in message ups.com... 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 groups.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." Thanks. The second solution did not work as it multiplies every number by 100000 - even one without an "m". I can use the first solution you gave me and write "0.3-m" in order not to confuse excel, unless you are willing to look at the second solution one more time and fix it. |
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) |