locked
Finding all Currency formatted Cells and change the Currency Symbol RRS feed

  • Question

  • Hello!

    I am trying to find out the easiest way to find all Currency formatted cell in a workbook and change the currency symbol based on a pick-list value (USD, AUD, EUR)

    how can I do that?

    any suggestion is much appreciated.

    many thanks

    Ivan


    Sunday, January 4, 2015 10:59 AM

Answers

  • Hi Ivan,

    I'm sorry but your phrase of pick-list value is not understood. However, do note that the easiest method of carrying out the said process on a sheet is:

    1. Ctrl+F to Find such cells;

    2. Change Look in: Option from Formulas (Default) to Values;

    3. Insert the currency symbol (e.g. $) in the Find What box;

    4. Press Find All button to list out all such cells;

    5. Press Ctrl+A to select all such cells;

    6. Press Esc button on keyboard to disappear the Find Box leaving such cells selected;

    7. Press Ctrl+1 to display the Format Cells dialog box;

    8. Select the Custom option;

    9. Insert a desired format in the Type Box like:

    _(USD * #,##0.00_);[Red]_(USD * (#,##0.00);_(Rs. * "-"??_);_(@_)]

    Best of luck!


    Thanx in advance, Best Regards, Faraz A Qureshi

    • Marked as answer by Caillen Tuesday, January 13, 2015 10:15 AM
    Sunday, January 4, 2015 8:19 PM
  • Hi Ivan,

    The solution by FARAZ is good, but seems that you want to do this programmatically. I just made a sample VBA code here, change currency symbol $ to €:

    Public Sub ChangeCurrencyFormattedCells()
        Dim WS As Worksheet
        Dim Search As String
        Dim rngFind As Range
        Dim firstCell As String
    
        Search = "$"
    
        For Each WS In Worksheets
            'Find currency formatted cell
            Set rngFind = WS.Cells.Find(What:=Search, LookIn:=xlValues, lookat:=xlPart)
            If Not rngFind Is Nothing Then firstCell = rngFind.Address
            Do While Not rngFind Is Nothing
                'Set currency format to €
                rngFind.Style = "Currency"
                rngFind.NumberFormat = "[$€-2] * #,##0.00"
                Set rngFind = WS.Cells.FindNext(After:=rngFind)
                If firstCell = rngFind.Address Then Exit Do
            Loop
        Next
    End Sub
    


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Caillen Tuesday, January 13, 2015 10:16 AM
    Monday, January 5, 2015 7:30 AM

All replies

  • Hi Ivan,

    I'm sorry but your phrase of pick-list value is not understood. However, do note that the easiest method of carrying out the said process on a sheet is:

    1. Ctrl+F to Find such cells;

    2. Change Look in: Option from Formulas (Default) to Values;

    3. Insert the currency symbol (e.g. $) in the Find What box;

    4. Press Find All button to list out all such cells;

    5. Press Ctrl+A to select all such cells;

    6. Press Esc button on keyboard to disappear the Find Box leaving such cells selected;

    7. Press Ctrl+1 to display the Format Cells dialog box;

    8. Select the Custom option;

    9. Insert a desired format in the Type Box like:

    _(USD * #,##0.00_);[Red]_(USD * (#,##0.00);_(Rs. * "-"??_);_(@_)]

    Best of luck!


    Thanx in advance, Best Regards, Faraz A Qureshi

    • Marked as answer by Caillen Tuesday, January 13, 2015 10:15 AM
    Sunday, January 4, 2015 8:19 PM
  • Hi Ivan,

    The solution by FARAZ is good, but seems that you want to do this programmatically. I just made a sample VBA code here, change currency symbol $ to €:

    Public Sub ChangeCurrencyFormattedCells()
        Dim WS As Worksheet
        Dim Search As String
        Dim rngFind As Range
        Dim firstCell As String
    
        Search = "$"
    
        For Each WS In Worksheets
            'Find currency formatted cell
            Set rngFind = WS.Cells.Find(What:=Search, LookIn:=xlValues, lookat:=xlPart)
            If Not rngFind Is Nothing Then firstCell = rngFind.Address
            Do While Not rngFind Is Nothing
                'Set currency format to €
                rngFind.Style = "Currency"
                rngFind.NumberFormat = "[$€-2] * #,##0.00"
                Set rngFind = WS.Cells.FindNext(After:=rngFind)
                If firstCell = rngFind.Address Then Exit Do
            Loop
        Next
    End Sub
    


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Caillen Tuesday, January 13, 2015 10:16 AM
    Monday, January 5, 2015 7:30 AM