Change French Canadian to US Dollars using VBA

[mnh]

Hello - we frequently spreadsheets completed in Quebec - so it's written in French Canadia. I'm having issues changing the currency from 55,00 $ and 1 555,00 $ to $55.00 and $1,555.00. I am not having any luck changing this to US dollars.

TARIFSÀLA JOURNÉE

59,00 $

59,00 $

60,00 $

62,00 $

Here is the code:

Union(searchRange, searchRange3).NumberFormat = "$#,##0.00"

Any help would be greatly appreciated! I'm using Excel 365 - Desktop.

 

[Scott Huish]

What is the purpose of searchRange and SearchRange3? Why can't you just format the whole column?
Are the $ in there already as text or is it a format?

 

[mnh]

What is the purpose of searchRange and SearchRange3? Why can't you just format the whole column?
Are the $ in there already as text or is it a format?

The information in imported from a contract onto the sheet, and not all of the columns line up with the same information. For example, Column K can have money amounts in rows 6-30, rows 35-52 are text and rows 54-70 are numbers only.

 

[mnh]

What is the purpose of searchRange and SearchRange3? Why can't you just format the whole column?
Are the $ in there already as text or is it a format?

Also, the fields that I need to change to US currency are already formatted as Currency - just not US Currency.

 

[Cubist]

What happens if you select the cells -> Ctrl + 1 -> Accounting -> Symbol = English (United States)?

 

[mnh]

What happens if you select the cells -> Ctrl + 1 -> Accounting -> Symbol = English (United States)?

Unfortunately, it doesn't change anything.

 

[Cubist]

Test the cell with =ISNUMBER(cell). Does it return TRUE or FALSE?

 

[mnh]

Test the cell with =ISNUMBER(cell). Does it return TRUE or FALSE?

Interesting - it's coming back as false! however, checking out the Format on the same cell, it shows Accounting.

 

[Joe4]

Interesting - it's coming back as false

That means you are dealing with Text entries, not numeric ones!
"Numbers entered as text" are NOT the same as valid numeric/date entry.
If you look at an entry in the formula bar, I bet you will see the $ in there, i.e. "59.00 $". So the " $" is literal text in your entry!
If it was a validly entered number, you would not see any currency sign when viewing the data in the formula bar.

however, checking out the Format on the same cell, it shows Accounting.

Since formatting ONLY affects valid number/date entries, they have no effect on Text entries.
You can change the format of those cells all you want, but they will have no impact on how the data is presented.
You need to convert your entries to valid numeric entries to get the cell formatting to apply to it.

 

[mnh]

OK - so I removed the dollar sign,

That means you are dealing with Text entries, not numeric ones!
"Numbers entered as text" are NOT the same as valid numeric/date entry.
If you look at an entry in the formula bar, I bet you will see the $ in there, i.e. "59.00 $". So the " $" is literal text in your entry!
If it was a validly entered number, you would not see any currency sign when viewing the data in the formula bar.


Since formatting ONLY affects valid number/date entries, they have no effect on Text entries.
You can change the format of those cells all you want, but they will have no impact on how the data is presented.
You need to convert your entries to valid numeric entries to get the cell formatting to apply to it.

You are correct - you see the $ in the formula bar! Thank you for pointing this out. So, how do I change this to a number using VBA? I can remove the " $" so I have 50,00 remaining - but from there, I am not sure where to go to change it to 50.00.