| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dollar / Euro sign
Using Excel 2003
I have a line in a VBA code because when I enter a '$' sign, the 'ぎ' sign is returned. (If Target.Value = "$" Then Range("B1").NumberFormat = "$" & "0.00") which should produce $123.00, for example. However, each time I run the macro, the 'ﾂ｣' and 'ぎ' signs work perfectly but the '$' sign always returns 'ぎ' (e.g. ぎ123.00). Is there a way of replacing the 'NumberFormat =' definition ("$" & "0.00") maybe by using an ASCII code, or CHR$ or any other method? Frustration is setting in! Any help gratefully received |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dollar / Euro sign
Yendorian,
If you want the entire worksheet formatted for US dollars try this: Select the block above the row and to the left of the column. This will select the whole worksheet. Select "Format", "Cells...",select currency. "Symbol:" select $. God Luck,hth :-) "Yendorian" wrote: Using Excel 2003 I have a line in a VBA code because when I enter a '$' sign, the 'ぎ' sign is returned. (If Target.Value = "$" Then Range("B1").NumberFormat = "$" & "0.00") which should produce $123.00, for example. However, each time I run the macro, the 'ﾂ｣' and 'ぎ' signs work perfectly but the '$' sign always returns 'ぎ' (e.g. ぎ123.00). Is there a way of replacing the 'NumberFormat =' definition ("$" & "0.00") maybe by using an ASCII code, or CHR$ or any other method? Frustration is setting in! Any help gratefully received |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dollar / Euro sign
tHi,
Thanks for that. Unfortuinately, I do not want the whole sheet converted to '$' format. The idea is, for example, that if A1 = "ﾂ｣" then B1, C1, D2, E3 will show the "ﾂ｣" format. But if A1="ぎ" those cells will show the "ぎ" format and if A1 is "$" the other cells should show the "$" format. But this is not the case - the "ﾂ｣" and "ぎ" work correctly and it is only the "$" that gives the problem by showing the "ぎ" format. "FloMM2" wrote: Yendorian, If you want the entire worksheet formatted for US dollars try this: Select the block above the row and to the left of the column. This will select the whole worksheet. Select "Format", "Cells...",select currency. "Symbol:" select $. God Luck,hth :-) "Yendorian" wrote: Using Excel 2003 I have a line in a VBA code because when I enter a '$' sign, the 'ぎ' sign is returned. (If Target.Value = "$" Then Range("B1").NumberFormat = "$" & "0.00") which should produce $123.00, for example. However, each time I run the macro, the 'ﾂ｣' and 'ぎ' signs work perfectly but the '$' sign always returns 'ぎ' (e.g. ぎ123.00). Is there a way of replacing the 'NumberFormat =' definition ("$" & "0.00") maybe by using an ASCII code, or CHR$ or any other method? Frustration is setting in! Any help gratefully received |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dollar / Euro sign
Yendorian,
If A1 is in the format that you desire, then you can select A1, select Edit,copy. Select the cells that have the wrong format, select edit, paste special. Then select "Formats". hth :-) "Yendorian" wrote: tHi, Thanks for that. Unfortuinately, I do not want the whole sheet converted to '$' format. The idea is, for example, that if A1 = "ﾂ｣" then B1, C1, D2, E3 will show the "ﾂ｣" format. But if A1="ぎ" those cells will show the "ぎ" format and if A1 is "$" the other cells should show the "$" format. But this is not the case - the "ﾂ｣" and "ぎ" work correctly and it is only the "$" that gives the problem by showing the "ぎ" format. "FloMM2" wrote: Yendorian, If you want the entire worksheet formatted for US dollars try this: Select the block above the row and to the left of the column. This will select the whole worksheet. Select "Format", "Cells...",select currency. "Symbol:" select $. God Luck,hth :-) "Yendorian" wrote: Using Excel 2003 I have a line in a VBA code because when I enter a '$' sign, the 'ぎ' sign is returned. (If Target.Value = "$" Then Range("B1").NumberFormat = "$" & "0.00") which should produce $123.00, for example. However, each time I run the macro, the 'ﾂ｣' and 'ぎ' signs work perfectly but the '$' sign always returns 'ぎ' (e.g. ぎ123.00). Is there a way of replacing the 'NumberFormat =' definition ("$" & "0.00") maybe by using an ASCII code, or CHR$ or any other method? Frustration is setting in! Any help gratefully received |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dollar / Euro sign
I was hoping to be able to use a macro that would avoid the necessity for
Edit, Copy, Paste Special etc. I think it is more a problem with Microsoft in that the system replaces the "$" automatically with the "ぎ" sign. Thanks anyway "FloMM2" wrote: Yendorian, If A1 is in the format that you desire, then you can select A1, select Edit,copy. Select the cells that have the wrong format, select edit, paste special. Then select "Formats". hth :-) "Yendorian" wrote: tHi, Thanks for that. Unfortuinately, I do not want the whole sheet converted to '$' format. The idea is, for example, that if A1 = "ﾂ｣" then B1, C1, D2, E3 will show the "ﾂ｣" format. But if A1="ぎ" those cells will show the "ぎ" format and if A1 is "$" the other cells should show the "$" format. But this is not the case - the "ﾂ｣" and "ぎ" work correctly and it is only the "$" that gives the problem by showing the "ぎ" format. "FloMM2" wrote: Yendorian, If you want the entire worksheet formatted for US dollars try this: Select the block above the row and to the left of the column. This will select the whole worksheet. Select "Format", "Cells...",select currency. "Symbol:" select $. God Luck,hth :-) "Yendorian" wrote: Using Excel 2003 I have a line in a VBA code because when I enter a '$' sign, the 'ぎ' sign is returned. (If Target.Value = "$" Then Range("B1").NumberFormat = "$" & "0.00") which should produce $123.00, for example. However, each time I run the macro, the 'ﾂ｣' and 'ぎ' signs work perfectly but the '$' sign always returns 'ぎ' (e.g. ぎ123.00). Is there a way of replacing the 'NumberFormat =' definition ("$" & "0.00") maybe by using an ASCII code, or CHR$ or any other method? Frustration is setting in! Any help gratefully received |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dollar / Euro sign
'Regional and Language Options' to English (United States) I think I've
discovered the problem. If I set my 'Regional and Language Options' to English (United States) the keys 'ﾂ｣', 'ぎ' and '$' produce 'ﾂ｣', 'ぎ' and '$' which is what I want. However, if I set the 'Regional and Language Options' to English (United Kingdom), the same keys produce 'ﾂ｣', 'ぎ' and 'ﾂ｣'. Then if I set my 'Regional and Language Options' to French (France), these keys produce 'ﾂ｣', 'ぎ' and 'ぎ'. So, it seems that when the settings are other than English (United States), the '$' defaults to that particular setting. All that is needed is to change the 'Regional and Language Options to English (United States). Hope this helps anyone who is having similar difficulties. "Yendorian" wrote: I was hoping to be able to use a macro that would avoid the necessity for Edit, Copy, Paste Special etc. I think it is more a problem with Microsoft in that the system replaces the "$" automatically with the "ぎ" sign. Thanks anyway "FloMM2" wrote: Yendorian, If A1 is in the format that you desire, then you can select A1, select Edit,copy. Select the cells that have the wrong format, select edit, paste special. Then select "Formats". hth :-) "Yendorian" wrote: tHi, Thanks for that. Unfortuinately, I do not want the whole sheet converted to '$' format. The idea is, for example, that if A1 = "ﾂ｣" then B1, C1, D2, E3 will show the "ﾂ｣" format. But if A1="ぎ" those cells will show the "ぎ" format and if A1 is "$" the other cells should show the "$" format. But this is not the case - the "ﾂ｣" and "ぎ" work correctly and it is only the "$" that gives the problem by showing the "ぎ" format. "FloMM2" wrote: Yendorian, If you want the entire worksheet formatted for US dollars try this: Select the block above the row and to the left of the column. This will select the whole worksheet. Select "Format", "Cells...",select currency. "Symbol:" select $. God Luck,hth :-) "Yendorian" wrote: Using Excel 2003 I have a line in a VBA code because when I enter a '$' sign, the 'ぎ' sign is returned. (If Target.Value = "$" Then Range("B1").NumberFormat = "$" & "0.00") which should produce $123.00, for example. However, each time I run the macro, the 'ﾂ｣' and 'ぎ' signs work perfectly but the '$' sign always returns 'ぎ' (e.g. ぎ123.00). Is there a way of replacing the 'NumberFormat =' definition ("$" & "0.00") maybe by using an ASCII code, or CHR$ or any other method? Frustration is setting in! Any help gratefully received |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dollar / Euro sign
Yendorian,
Let me see if I now understand: Case 1: If cell A1 format is in British pounds, you would like the cells B1,C1,D2 & E3 to match this format. Case 2: If cell A1 format is in dollars (US), you would like the cells B1,C1,D2 & E3 to match this format. Case 3 : If cell A1 format is in Euros, you would like the cells B1,C1,D2 & E3 to match this format. And you would like it to be a Macro that you run. "Yendorian" wrote: 'Regional and Language Options' to English (United States) I think I've discovered the problem. If I set my 'Regional and Language Options' to English (United States) the keys 'ﾂ｣', 'ぎ' and '$' produce 'ﾂ｣', 'ぎ' and '$' which is what I want. However, if I set the 'Regional and Language Options' to English (United Kingdom), the same keys produce 'ﾂ｣', 'ぎ' and 'ﾂ｣'. Then if I set my 'Regional and Language Options' to French (France), these keys produce 'ﾂ｣', 'ぎ' and 'ぎ'. So, it seems that when the settings are other than English (United States), the '$' defaults to that particular setting. All that is needed is to change the 'Regional and Language Options to English (United States). Hope this helps anyone who is having similar difficulties. "Yendorian" wrote: I was hoping to be able to use a macro that would avoid the necessity for Edit, Copy, Paste Special etc. I think it is more a problem with Microsoft in that the system replaces the "$" automatically with the "ぎ" sign. Thanks anyway "FloMM2" wrote: Yendorian, If A1 is in the format that you desire, then you can select A1, select Edit,copy. Select the cells that have the wrong format, select edit, paste special. Then select "Formats". hth :-) "Yendorian" wrote: tHi, Thanks for that. Unfortuinately, I do not want the whole sheet converted to '$' format. The idea is, for example, that if A1 = "ﾂ｣" then B1, C1, D2, E3 will show the "ﾂ｣" format. But if A1="ぎ" those cells will show the "ぎ" format and if A1 is "$" the other cells should show the "$" format. But this is not the case - the "ﾂ｣" and "ぎ" work correctly and it is only the "$" that gives the problem by showing the "ぎ" format. "FloMM2" wrote: Yendorian, If you want the entire worksheet formatted for US dollars try this: Select the block above the row and to the left of the column. This will select the whole worksheet. Select "Format", "Cells...",select currency. "Symbol:" select $. God Luck,hth :-) "Yendorian" wrote: Using Excel 2003 I have a line in a VBA code because when I enter a '$' sign, the 'ぎ' sign is returned. (If Target.Value = "$" Then Range("B1").NumberFormat = "$" & "0.00") which should produce $123.00, for example. However, each time I run the macro, the 'ﾂ｣' and 'ぎ' signs work perfectly but the '$' sign always returns 'ぎ' (e.g. ぎ123.00). Is there a way of replacing the 'NumberFormat =' definition ("$" & "0.00") maybe by using an ASCII code, or CHR$ or any other method? Frustration is setting in! Any help gratefully received |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dollar / Euro sign
Yendornian,
Here is what I came up with: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 6/30/2007 by Dennis L. Follis ' ' Worksheets("Sheet??").Activate Worksheets("Sheet??").Range("A1").Select If Selection.NumberFormat = "$#,##0.00" Then Range("B1:C1").Select Selection.NumberFormat = "$#,##0.00" Range("D2").Select Selection.NumberFormat = "$#,##0.00" Range("E3").Select Selection.NumberFormat = "$#,##0.00" Else If Selection.NumberFormat = "[$ﾂ｣-809]#,##0.00" Then Range("B1:C1").Select Selection.NumberFormat = "[$ﾂ｣-809]#,##0.00" Range("D2").Select Selection.NumberFormat = "[$ﾂ｣-809]#,##0.00" Range("E3").Select Selection.NumberFormat = "[$ﾂ｣-809]#,##0.00" Else If Selection.NumberFormat = "[$ぎ-2] #,##0.00" Then Range("B1:C1").Select Selection.NumberFormat = "[$ぎ-2] #,##0.00" Range("D2").Select Selection.NumberFormat = "[$ぎ-2] #,##0.00" Range("E3").Select End If End If End If End Sub Copy this from "Sub Macro1()" all the way through "End Sub" The line of code:"Worksheets("Sheet??").Activate Worksheets("Sheet??").Range("A1").Select You will have to replace the "??" with the Sheet number. This is typically 1. So it will look like: Worksheets("Sheet1").Activate Worksheets("Sheet1").Range.("A1").Select HTH :-) "Yendorian" wrote: 'Regional and Language Options' to English (United States) I think I've discovered the problem. If I set my 'Regional and Language Options' to English (United States) the keys 'ﾂ｣', 'ぎ' and '$' produce 'ﾂ｣', 'ぎ' and '$' which is what I want. However, if I set the 'Regional and Language Options' to English (United Kingdom), the same keys produce 'ﾂ｣', 'ぎ' and 'ﾂ｣'. Then if I set my 'Regional and Language Options' to French (France), these keys produce 'ﾂ｣', 'ぎ' and 'ぎ'. So, it seems that when the settings are other than English (United States), the '$' defaults to that particular setting. All that is needed is to change the 'Regional and Language Options to English (United States). Hope this helps anyone who is having similar difficulties. "Yendorian" wrote: I was hoping to be able to use a macro that would avoid the necessity for Edit, Copy, Paste Special etc. I think it is more a problem with Microsoft in that the system replaces the "$" automatically with the "ぎ" sign. Thanks anyway "FloMM2" wrote: Yendorian, If A1 is in the format that you desire, then you can select A1, select Edit,copy. Select the cells that have the wrong format, select edit, paste special. Then select "Formats". hth :-) "Yendorian" wrote: tHi, Thanks for that. Unfortuinately, I do not want the whole sheet converted to '$' format. The idea is, for example, that if A1 = "ﾂ｣" then B1, C1, D2, E3 will show the "ﾂ｣" format. But if A1="ぎ" those cells will show the "ぎ" format and if A1 is "$" the other cells should show the "$" format. But this is not the case - the "ﾂ｣" and "ぎ" work correctly and it is only the "$" that gives the problem by showing the "ぎ" format. "FloMM2" wrote: Yendorian, If you want the entire worksheet formatted for US dollars try this: Select the block above the row and to the left of the column. This will select the whole worksheet. Select "Format", "Cells...",select currency. "Symbol:" select $. God Luck,hth :-) "Yendorian" wrote: Using Excel 2003 I have a line in a VBA code because when I enter a '$' sign, the 'ぎ' sign is returned. (If Target.Value = "$" Then Range("B1").NumberFormat = "$" & "0.00") which should produce $123.00, for example. However, each time I run the macro, the 'ﾂ｣' and 'ぎ' signs work perfectly but the '$' sign always returns 'ぎ' (e.g. ぎ123.00). Is there a way of replacing the 'NumberFormat =' definition ("$" & "0.00") maybe by using an ASCII code, or CHR$ or any other method? Frustration is setting in! Any help gratefully received |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dollar / Euro sign
Hi FloMM2
Thanks for all the work you have done on this. I'm in the process of adapting it to my workbook but it looks as if it should work. "FloMM2" wrote: Yendornian, Here is what I came up with: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 6/30/2007 by Dennis L. Follis ' ' Worksheets("Sheet??").Activate Worksheets("Sheet??").Range("A1").Select If Selection.NumberFormat = "$#,##0.00" Then Range("B1:C1").Select Selection.NumberFormat = "$#,##0.00" Range("D2").Select Selection.NumberFormat = "$#,##0.00" Range("E3").Select Selection.NumberFormat = "$#,##0.00" Else If Selection.NumberFormat = "[$ﾂ｣-809]#,##0.00" Then Range("B1:C1").Select Selection.NumberFormat = "[$ﾂ｣-809]#,##0.00" Range("D2").Select Selection.NumberFormat = "[$ﾂ｣-809]#,##0.00" Range("E3").Select Selection.NumberFormat = "[$ﾂ｣-809]#,##0.00" Else If Selection.NumberFormat = "[$ぎ-2] #,##0.00" Then Range("B1:C1").Select Selection.NumberFormat = "[$ぎ-2] #,##0.00" Range("D2").Select Selection.NumberFormat = "[$ぎ-2] #,##0.00" Range("E3").Select End If End If End If End Sub Copy this from "Sub Macro1()" all the way through "End Sub" The line of code:"Worksheets("Sheet??").Activate Worksheets("Sheet??").Range("A1").Select You will have to replace the "??" with the Sheet number. This is typically 1. So it will look like: Worksheets("Sheet1").Activate Worksheets("Sheet1").Range.("A1").Select HTH :-) "Yendorian" wrote: 'Regional and Language Options' to English (United States) I think I've discovered the problem. If I set my 'Regional and Language Options' to English (United States) the keys 'ﾂ｣', 'ぎ' and '$' produce 'ﾂ｣', 'ぎ' and '$' which is what I want. However, if I set the 'Regional and Language Options' to English (United Kingdom), the same keys produce 'ﾂ｣', 'ぎ' and 'ﾂ｣'. Then if I set my 'Regional and Language Options' to French (France), these keys produce 'ﾂ｣', 'ぎ' and 'ぎ'. So, it seems that when the settings are other than English (United States), the '$' defaults to that particular setting. All that is needed is to change the 'Regional and Language Options to English (United States). Hope this helps anyone who is having similar difficulties. "Yendorian" wrote: I was hoping to be able to use a macro that would avoid the necessity for Edit, Copy, Paste Special etc. I think it is more a problem with Microsoft in that the system replaces the "$" automatically with the "ぎ" sign. Thanks anyway "FloMM2" wrote: Yendorian, If A1 is in the format that you desire, then you can select A1, select Edit,copy. Select the cells that have the wrong format, select edit, paste special. Then select "Formats". hth :-) "Yendorian" wrote: tHi, Thanks for that. Unfortuinately, I do not want the whole sheet converted to '$' format. The idea is, for example, that if A1 = "ﾂ｣" then B1, C1, D2, E3 will show the "ﾂ｣" format. But if A1="ぎ" those cells will show the "ぎ" format and if A1 is "$" the other cells should show the "$" format. But this is not the case - the "ﾂ｣" and "ぎ" work correctly and it is only the "$" that gives the problem by showing the "ぎ" format. "FloMM2" wrote: Yendorian, If you want the entire worksheet formatted for US dollars try this: Select the block above the row and to the left of the column. This will select the whole worksheet. Select "Format", "Cells...",select currency. "Symbol:" select $. God Luck,hth :-) "Yendorian" wrote: Using Excel 2003 I have a line in a VBA code because when I enter a '$' sign, the 'ぎ' sign is returned. (If Target.Value = "$" Then Range("B1").NumberFormat = "$" & "0.00") which should produce $123.00, for example. However, each time I run the macro, the 'ﾂ｣' and 'ぎ' signs work perfectly but the '$' sign always returns 'ぎ' (e.g. ぎ123.00). Is there a way of replacing the 'NumberFormat =' definition ("$" & "0.00") maybe by using an ASCII code, or CHR$ or any other method? Frustration is setting in! Any help gratefully received |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how can i change dollar sign to rupee sign in sales invoice | Excel Discussion (Misc queries) | |||
| Dollar Sign | Excel Discussion (Misc queries) | |||
| How can I change dollar sybbol to euro symbol? | New Users to Excel | |||
| XL invoice replace the dollar sign with euro sign | New Users to Excel | |||
| Can I disply Euro and Dollar Formats in the same spreadsheet | Excel Discussion (Misc queries) | |||
Linear Mode
