+ Reply to Thread
Results 1 to 12 of 12

Combining Customized Number-Format with the Accounting Format to get a dash "-" when zero?

  1. 08-13-2012, 09:47 AM #1
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Combining Customized Number-Format with the Accounting Format to get a dash "-" when zero?

    Combining Customized Number-Format with the Accounting Format to get a dash "-" when zero?

    Dear Forum,

    I am not sure whether this is possible to be done Manually and therefore I put this query in the Programming in case of any VBA intervention..

    I often use a particular format for displaying Indian Currency with a "Rs" prefix with the number values, which is quite popular at my work-place.
    However, recently I also came across an Accounting Format for plain numbers which displays a simple Dash "-" when the values of the cell is zero..

    I would like to have this "-" in my existing Format when the cells contain a zero..
    The Great thing about the Accounting Format is that it displays the "-" in the center which looks cool, but it also makes a Normal Number in the center too.

    In my Number-Format I always keep the Number with decimals and without decimals Horizontal Right Aligned..
    So is there a possibility to combine the best of Both and Get a New Format which displays the "-" when no values and also numbers as in the existing format..

    Please find the formula for the Rupees Number Format:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Also one small request I want to increase the Range from 9999999 to more, so how can that be done I am using this from the NET..

    Warm Regards
    e4excel
    Reply With QuoteRegister To Reply
  2. 08-13-2012, 09:52 AM #2
    mikerickson
    mikerickson is offline
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Combining Customized Number-Format with the Accounting Format to get a dash "-" when z

    Try
    [>9999999]"Rs "#\,##\,##\,##0.00;[>99999]"Rs "#\,##\,##0.00;"-"
    Last edited by mikerickson; 08-13-2012 at 09:54 AM.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.
    Reply With QuoteRegister To Reply
  3. 08-13-2012, 09:58 AM #3
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Combining Customized Number-Format with the Accounting Format to get a dash "-" when z

    Hi Mike,

    Thanks for such a quick response but that was a for a larger number than the one I am using right..
    But the commas need to be different so how do I do that?

    Since this Format is not created by me I am not sure what I need to do to get the commas in a proper manner, but in Indian style we have commas in a different way than other countries..

    Can you please explain a bit and also the main thing to combine the two can that be done? at all in the first place ?

    Thanks in advance
    Reply With QuoteRegister To Reply
  4. 08-13-2012, 10:09 AM #4
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Combining Customized Number-Format with the Accounting Format to get a dash "-" when z

    Sorry Mike I did not realise that you had already given me a solution I missed the "-" in the end..
    My apologies..

    But can you please also tell me how Do i change the Alignment for a Number to be Right ALigned and for a dash to be Center ALigned..

    And also how do I increase the Range please explain..

    Thanks again..
    Reply With QuoteRegister To Reply
  5. 08-13-2012, 03:45 PM #5
    mikerickson
    mikerickson is offline
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Combining Customized Number-Format with the Accounting Format to get a dash "-" when z

    Alignment cannot be control with a number format.
    Reply With QuoteRegister To Reply
  6. 08-13-2012, 04:36 PM #6
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Combining Customized Number-Format with the Accounting Format to get a dash "-" when z

    Hey Mike, Now I tried it again but it did not work..
    I used the Format you gave and pasted in the Custom's Text Box but it displays a dash for all the numbers irrespective whether there's an zero or not?

    Using VBA code will controling the Format be a possibilty?
    Like if I keep a run a check on the numbers in a range of cells from lets say A2 till A12 and wherever there's an zero then the format of that cell changed to the Accounting and so the dash gets center-aligned and for a Number which is not a zero I can retain the existing Indian Currency Format..

    Can you also please suggest me on how can I expand the same format for larger numbers than mentioned in my earlier format..

    Thanks in advance..

    Warm Regards
    e4excel

    ---------- Post added at 02:06 AM ---------- Previous post was at 01:59 AM ----------

    So Dumb am I ?
    I had to slightly modify it as it was only for larger Ranges so it worked earlier and now when i tried it with smaller nos it did not work..

    I tweaked it again and it worked fine.Thanks..once again and sorry for not understanding that but can you please give me some suggestions in setting the Alignment using VBA?

    Now again when I try changing the number to a zero it remained and did not change to a dash..some ANOMALY
    Last edited by e4excel; 08-13-2012 at 04:40 PM.
    Reply With QuoteRegister To Reply
  7. 08-13-2012, 05:11 PM #7
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Combining Customized Number-Format with the Accounting Format to get a dash "-" when z

    I came up with my own small VBA Code but am not sure whether thats correct or not though it worked..
    Please advise..

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 08-14-2012, 06:08 AM #8
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Combining Customized Number-Format with the Accounting Format to get a dash "-" when z

    Dear Forum,

    I am trying to do this but am getting an error and I am no an expert in VBA but trying after ging through some of my past threads where I have received help..

    Please correct me and advise whats the mistake in the below mentioned code.

    Please Login or Register  to view this content.
    The below Sub is called in the above Sub which is just a Trial, the actual Sub will be in the WorkSheet_Change event..

    Please Login or Register  to view this content.
    Last edited by e4excel; 08-14-2012 at 12:30 PM.
    Reply With QuoteRegister To Reply
  9. 08-14-2012, 11:27 AM #9
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Combining Customized Number-Format with the Accounting Format to get a dash "-" when z

    I am getting an Error in the Line

    Set rngSingleColumn = rngSingleColumn

    For Each cell In rngSingleColumn

    If .Value = 0 Then <--- This Line
    Please advise what is wrong in this if I am Setting the Range in the first line then???

    e4excel
    Reply With QuoteRegister To Reply
  10. 08-14-2012, 12:05 PM #10
    romperstomper
    romperstomper is offline
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Combining Customized Number-Format with the Accounting Format to get a dash "-" when z

    There is no With statement, so to what do you think .Value refers?
    Remember what the dormouse said
    Feed your head
    Reply With QuoteRegister To Reply
  11. 08-14-2012, 12:27 PM #11
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Combining Customized Number-Format with the Accounting Format to get a dash "-" when z

    Hi RomperStomper,

    I am so glad to get your response on this as I had made this VBA coding style as per the help you and Colin had provided to me on this thread some months back..

    http://www.excelforum.com/excel-prog...selection.html

    I had actually put up a small note on the above link to thank both you however as my net was bad the note got lost..

    Anyways.

    Regarding this query..

    I have the With Statement in the Sub where I am calling this Swap_NumberFormat so then that's why I did not put up a with statement there as this structure would be used somewhere else the way you had explained in the other thread to pass the reference..

    Please check the edited post#8

    Thanks in advance..
    Last edited by e4excel; 08-14-2012 at 12:30 PM.
    Reply With QuoteRegister To Reply
  12. 08-16-2012, 09:49 AM #12
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Bump No Response

    Bump No Response

    Some suggestions would really help me please
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1