Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Aug 2003
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change $ to other currencies in entire model? (XP)

    How do I change all the cells using data as currency in Dollars to a different currency such as Euros? My model is large and complex, with other cells in other units, so I cannot select all and make a simple change.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Change $ to other currencies in entire model? (XP)

    It is dependent on the regional settings in windows:

    start - settings- control panel-regional options- currency select a different symbol <ok>

    The only other way is to to change the format of all the cells from your current currency format to one that replaces th "$" with something else. This could be done via a macro, that searches every cell and using SUBSTITUTE to change the numberformat of each cell to replace the "$" with some other character.

    This should replace them all with the Euro (the "$" is replaced in format by "[$

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Change $ to other currencies in entire model? (XP)

    In the example you suggested (dollars to euros) you could create a style for Euros with the appropriate formatting, name it Euro, and run some code like the following :

    Dim c As Range
    For Each c In Union(Cells.SpecialCells(xlCellTypeConstants, 1), _
    Cells.SpecialCells(xlCellTypeFormulas, 1))
    If InStr(1, c.NumberFormatLocal, "$") > 0 Then
    c.Style = "Euro"
    End If
    Next

    You could also include a conversion value if required.

    Andrew C

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change $ to other currencies in entire model?

    If you wish to change your default Currency format to EUR for Excel only, you can go to Format|Style and Modify the Currency Style. This will, however, apply to all future Excel Currency formats - unless you replace your *.xlb file (which contains all your customisations). HTH
    Gre

Posting Permissions

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