none
Excel Column number format with decimal places

    Question

  • Hii
    I am working on excel 2007 add ins using vb.net 2005. and i want to sent the excel column number format with the decimal places.
    i do the following
    Columns(5).NumberFormat="@"
    but it shows the number with decimal places but it not working in cells they contains formula.
    Please suggest how i set the column number format with decimal places.
    Thanx
    Mitesh
    Monday, July 14, 2008 4:21 PM

Answers

  • Hi Mitesh,

     

    The common way to know which string to set for the format is recording a VBA macro, and sees what is set by Excel when we manually do it.

    You can record and view the Macro by the following steps:

    1.      Open Excel, and navigate the Ribbon to Developer Tab

    2.      Click Record Macro button in Code Group. Record Macro dialog pops up, click OK. (Now, most of your operation are recorded in VBA codes at backgroup)

    3.      Consequently, you can manually set cell’s format to what you want manually, in Italian version.

    4.      Navigate back to Developer Tab and click Stop recording button in Code Group.

    5.      Press Alt+F11, VBA editor pops up. In Project Window, find Module1 in Modules, and double click it, you will find VBA codes corresponding to what you did.

     

     

    Thanks,

    Ji

     

    Friday, July 18, 2008 6:54 AM
    Moderator

All replies

  • Hi Mitesh

     

    Selection.NumberFormat = "0.00" will work if the current cell is selected and doesn't matter if there is a formula to calculate this.

     

    Regards

     

    Wednesday, July 16, 2008 9:17 PM
    Moderator
  • Hiii
    i set the following thing.

    Selection.NumberFormat = "0.00"

    But it works properly in English version. but it not working properly in Italian version.
    if i enter the value 10.20 it shows the 0.010 value.
    Please suggest.
    Thanx
    ~Khatri Mitesh
    Thursday, July 17, 2008 7:04 AM
  • Hi,

    if English Version works you could use 

    System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-US");

    to specify the Culture.

    Thanks

    Thursday, July 17, 2008 8:54 AM
  • Hi Mitesh,

     

    The common way to know which string to set for the format is recording a VBA macro, and sees what is set by Excel when we manually do it.

    You can record and view the Macro by the following steps:

    1.      Open Excel, and navigate the Ribbon to Developer Tab

    2.      Click Record Macro button in Code Group. Record Macro dialog pops up, click OK. (Now, most of your operation are recorded in VBA codes at backgroup)

    3.      Consequently, you can manually set cell’s format to what you want manually, in Italian version.

    4.      Navigate back to Developer Tab and click Stop recording button in Code Group.

    5.      Press Alt+F11, VBA editor pops up. In Project Window, find Module1 in Modules, and double click it, you will find VBA codes corresponding to what you did.

     

     

    Thanks,

    Ji

     

    Friday, July 18, 2008 6:54 AM
    Moderator
  • yes, is it correct that the NumberFormat settings will only work in my culture and if I need to move on computer with another culture settings, it will fail? How can I make NumberFormat settings culture independant?
    Wednesday, November 19, 2008 11:40 AM
  • I am facing a similar issue, Where I am trying to specify the numberformat to a range in an Excel automation (Excel 2003 / .Net Framework 2)

    We are trying to acheive formatting a range object to show the color red for negetive numbers,

    range.NumberFormatLocal = "#,##0_);[RED](#,##0)";

    This works fine in computers with english locale, but failes in PC where the local is one of the far east local. 

    We have tried the following (per someone's suggestions) but that did not work either for far east computers.

    range.NumberFormatLocal = "#,##0_);[COLOR 3](#,##0)";

    Is there any way we could set this up so the numberformat is locale independent.

    Would appreciate your help.

    Thanks.

    Siddharth

    Monday, February 07, 2011 2:51 AM