Currency Conversion - Changing the Cell Formats

[miwoodar]

Greetings -

I have a number of cost estimates that I am trying to convert to a different currency (US Dollars to UK Pounds). The math is done...now for the formatting. The sheet has thousands of cells that have not been formatted in a similar manner. Some have a currency format, some have an accounting format, some are custom formats. All show a dollar sign due to the formatting selected. On top of that, the decimal places are not consistent.

I have written code to swap formats; however, I have no way of knowing just how many formats will be encountered. As a result, I'm having to go through each sheet and check each cell visually and adjust those that were not captured.

Is there an easy way to write a line that will capture all possible dollar formats?

____________________
Mike

 

[SkipVought]

Hi,

Check out the NumberFormat property.

But could you not select the COLUMNS that should contain pounds sterling, and set the format in one swell foop?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[miwoodar]

I can't do it by columns - the sheets contain many interspersed supporting calcs to calculate the quantities.

I'll look at the number format property. Will that let me distinguish between the quantities and the costs?

____________________
Mike

 

[SkipVought]

Have you LOOKED at the Dollar format and the Pound format?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[miwoodar]

I'm sorry but where are you telling me to look exactly? I checked the help file - not very helpful. I also looked via the browser window. Is there a better place to learn more about this?

____________________
Mike

 

[combo]

You could loop through the cells in used range and test each cell's .Text property (displayed text) for $s.
If found, change formatting/value. Probably it would be useful to build an array of found custom numberformats and at the end of searching delete them.

combo

 

[SkipVought]

NumberFormat has stuff in it. Have you looked at the NumberFormat for the FORMATS of interest?

Debug. Watch Window.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[miwoodar]

You guys are talking right over my head. I only get a chance to write code for about 20 hours every other month. It's not in my job desciption but I realize it's usefulness and can usually write meaningful code without asking for help. I don't know how to build and array or make use the debug/watch windows. This is the approach I've been doing it up to now (a series of similar statements):

Application.FindFormat.NumberFormat = "[$$-409]#,##0.00"
Application.ReplaceFormat.NumberFormat = "[$£-809]#,##0.00"
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True

____________________
Mike

 

[SkipVought]

and what's the problem with that?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[miwoodar]

This works until I get to the endless possibilities of custom formats.

____________________
Mike

 

[SkipVought]

Why can't you change $$ to $£?



Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[miwoodar]

Like this? It doesn't appear to work.

Application.FindFormat.NumberFormat = "[$$]"
Application.ReplaceFormat.NumberFormat = "[$£]"
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True

____________________
Mike

 

[SkipVought]

That's assuming that the WHOLE format is as stated.

Find a format.

Assign a string to the format.

replace in the string as suggested.

Use string in replaceformat.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[miwoodar]

So I got this to work...sort of. Here's what I ended up doing before I abandoned the effort. It took about 2 minutes to go through all 15 sheets with the calculation and screen updating turned off. As a result, I ended up removing all dollar signs and all pound signs then added a cell to the top of each sheet that stated what currency was being used.

Sheets(("Cash Flow Summary")).Select

Range("A1").Select
On Error Resume Next

Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Application.FindFormat.NumberFormat = "[$$-409]#,##0"
Application.ReplaceFormat.NumberFormat = "[$£-809]#,##0"
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True


Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Application.FindFormat.NumberFormat = "[$$-409]#,##0.00"
Application.ReplaceFormat.NumberFormat = "[$£-809]#,##0.00"
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True


Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Application.FindFormat.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Application.ReplaceFormat.NumberFormat = "[$£-809]#,##0"
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True

Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Application.FindFormat.NumberFormat = "$#,##0"
Application.ReplaceFormat.NumberFormat = "[$£-809]#,##0"
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True

Application.FindFormat.Clear
Application.FindFormat.NumberFormat = "$#,##0.00"
Application.ReplaceFormat.Clear
Application.ReplaceFormat.NumberFormat = "[$£-809]#,##0.00"
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True




____________________
Mike

 

[miwoodar]

Oh - and I never found a good way to address the custom formats (I stopped counting at a half dozen).

____________________
Mike