How to search for & change the currency symbol across different formats

[Sipa]

Hello

This is my first post to Chandoo forums.

I have an Excel 2010 workbook containing several sheets. Different currency formats are used throughout the sheets, these are currently using the $ symbol.
I would like to be able to choose the symbol to be used, either $, £ or € and have this replace the currently used symbol in all occurrences where any type of currency format is used.

After a lot of searching have tried several macro's to do this , but my programming skills are not very good so I am asking for help now.

Thank you

 

[bobhc]

Why not just use the find and replace function?

 

[Sipa]

Hi bobhc
Thanks for taking the time to reply.
I'd like to be able to choose the symbol and execute the find & replace across multiple sheets in one go.

 

[Sipa]

Could this macro be adapted to look at a dropdown box for the required symbols.

Public Sub UpdateFormats()
  Dim rFind As Range
  Dim rReplace As Range
  Dim rNextCell As Range
  Dim sNewFormat As String
  Dim sOldFormat As String
  Dim ws As Worksheet

  On Error Resume Next

  ' Determine the old format
  Do
  Set rFind = Application.InputBox( _
  prompt:="Select a cell that uses the format " & _
  "for which you want to search", _
  Type:=8)

  If rFind Is Nothing Then
  If MsgBox("Do you want to quit?", vbYesNo) = vbYes Then
  Exit Sub
  ElseIf InStr(1, rFind.Address, ":", vbTextCompare) > 0 Then
  MsgBox "Please select only one cell."
  Set rFind = Nothing
  End If
  End If
  Loop Until Not rFind Is Nothing
  sOldFormat = rFind.NumberFormat

  ' Determine the new format
  Do
  Set rReplace = Application.InputBox( _
  prompt:="Select a cell using the new format", _
  Type:=8)

  If rReplace Is Nothing Then
  If MsgBox("Do you want to quit?", vbYesNo) = vbYes Then
  Exit Sub
  ElseIf InStr(1, rReplace.Address, ":", vbTextCompare) > 0 Then
  MsgBox "Please select only one cell."
  Set rReplace = Nothing
  End If
  End If
  Loop Until Not rReplace Is Nothing
  sNewFormat = rReplace.NumberFormat

  ' Do the replacing
  For Each ws In ActiveWorkbook.Worksheets
  For Each rNextCell In ws.UsedRange
  If rNextCell.NumberFormat = sOldFormat Then
  rNextCell.NumberFormat = sNewFormat
  End If
  Next rNextCell
  Next ws
  MsgBox "The selected format has been changed."
End Sub

[code]

 

[bobhc]

execute the find & replace across multiple sheets in one go.

Just use choose workbook in find/replace options

 

[Sipa]

I'm sorry but using find and replace just does not work.

 

[bobhc]

It does, but it does have a quirk some times, instead of entering the symbols copy/paste

 

[Sipa]

Doing it that way, doesn't suite my purpose.
What I need is a macro to do the search and replace of the currency symbol only, leaving any formatting and cell contents alone. this should be done over all sheets in the workbook, of which there are many.