| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dollar/Euro/Pound Excel macro
In EXCEL 2003, i am transferring data from Sheet1 to Sheet2. The data
includes one of the three currency signs ($,ぎ,ﾂ｣) in column 1 each time. Can anyone help with a macro that will read the currency sign and then apply that currency sign and format to other cells in the same row (C,D,E)- the same process being repeated each time a new row of data is added. e.g. A B C D E F 1 $ 25.00 $ 67.05 $ 98.00 $ 102.85 67.00 2 ﾂ｣ 34.00 ﾂ｣ 57.64 ﾂ｣ 87.00 ﾂ｣ 111.45 54.00 3 $ 198.00 $ 58.97 $ 25.00 $ 68.30 258.00 4 ぎ 62.57 ぎ 85.21 ぎ 58.00 ぎ 98.35 5.00 5 ﾂ｣ 7.54 ﾂ｣ 1.50 ﾂ｣ 69.00 ﾂ｣ 0.65 4.65 etc Many thanks for all the advice given - this is a steep but fascinating learning curve! Yendorian |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dollar/Euro/Pound Excel macro
Select the worksheet where you want this automatic formatting to take place
in columns C, D and E. Right-Click on the sheet's name tab, then choose [View Code]. Copy and paste the code below into the code module that is presented to you in the VB Editor. What this does is detect any change in value in columns C, D or E on that sheet, and then test to see if one of the special characters is 1st character in column A on that sheet, and if so, sets formatting for the cell that changed value accordingly. If this isn't what you desired, let me know. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 3 Or Target.Column 5 Then 'not in C, D or E Exit Sub End If On Error GoTo LeaveChange Select Case Range("A" & Target.Row) Case Is = Chr$(36) ' U.S. dollar symbol Target.NumberFormat = "$#,##0.00" Case Is = Chr$(128) ' Euro symbol Target.NumberFormat = "[$ぎ-2] #,##0.00" Case Is = Chr$(163) ' British Pound symbol Target.NumberFormat = "[$ﾂ｣-809]#,##0.00" Case Else 'do nothing - no special symbol in A# End Select ExitSheetChange: On Error GoTo 0 ' clear error trapping Exit Sub LeaveChange: Resume ExitSheetChange End Sub "Yendorian" wrote: In EXCEL 2003, i am transferring data from Sheet1 to Sheet2. The data includes one of the three currency signs ($,ぎ,ﾂ｣) in column 1 each time. Can anyone help with a macro that will read the currency sign and then apply that currency sign and format to other cells in the same row (C,D,E)- the same process being repeated each time a new row of data is added. e.g. A B C D E F 1 $ 25.00 $ 67.05 $ 98.00 $ 102.85 67.00 2 ﾂ｣ 34.00 ﾂ｣ 57.64 ﾂ｣ 87.00 ﾂ｣ 111.45 54.00 3 $ 198.00 $ 58.97 $ 25.00 $ 68.30 258.00 4 ぎ 62.57 ぎ 85.21 ぎ 58.00 ぎ 98.35 5.00 5 ﾂ｣ 7.54 ﾂ｣ 1.50 ﾂ｣ 69.00 ﾂ｣ 0.65 4.65 etc Many thanks for all the advice given - this is a steep but fascinating learning curve! Yendorian |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dollar/Euro/Pound Excel macro
Hi JL,
Thanks for your reply and work. this is exactly what I need. I tried the macro in a new workbook and it worked excellently except that it returned what seem like random signs in the cells after (E) instead of leaving the original format (Number). (It was as if it was not reading the OR part of the 'IF Target.Column <3 Or Target.Column 5' line). I then incorporated it into the prog I am working on - and nothing happened ! So I am doing something wrong somewhere. Here is how I altered your macro: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 61 Or Target.Column 69 Then 'not in A(1) to BH(60) or in BR(70) onwards Exit Sub End If On Error GoTo LeaveChange 'currency code (ﾂ｣,$,ぎ) is in column BH Select Case Range("BH" & Target.Row) Case Is = Chr$(36) ' U.S. dollar symbol Target.NumberFormat = "$#,##0.00" Case Is = Chr$(128) ' Euro symbol Target.NumberFormat = "[$ぎ-2] #,##0.00" Case Is = Chr$(163) ' British Pound symbol Target.NumberFormat = "[$ﾂ｣-809]#,##0.00" Case Else 'do nothing - no special symbol in A# End Select ExitSheetChange: On Error GoTo 0 ' clear error trapping Exit Sub LeaveChange: Resume ExitSheetChange End Sub The currency code is now in column BH (60) and the cells I want to change are from BI (61) to BQ (69) inclusive. Could I be a pain and ask how to add that, in as well as the above macro, figures in cells AT (46) to BG (59) inclusive should be in ﾂ｣ sterling format and those in BR (70) to BZ (78) inclusive should always have the ぎ euro sign. I think I have got the column numbers correct. I've only been into VBA for a few weeks but explanations like yours are extremely helpful. (Pity ordinary BASIC is not used now - I had almost mastered that!!) Thanks once again "JLatham" wrote: Select the worksheet where you want this automatic formatting to take place in columns C, D and E. Right-Click on the sheet's name tab, then choose [View Code]. Copy and paste the code below into the code module that is presented to you in the VB Editor. What this does is detect any change in value in columns C, D or E on that sheet, and then test to see if one of the special characters is 1st character in column A on that sheet, and if so, sets formatting for the cell that changed value accordingly. If this isn't what you desired, let me know. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 3 Or Target.Column 5 Then 'not in C, D or E Exit Sub End If On Error GoTo LeaveChange Select Case Range("A" & Target.Row) Case Is = Chr$(36) ' U.S. dollar symbol Target.NumberFormat = "$#,##0.00" Case Is = Chr$(128) ' Euro symbol Target.NumberFormat = "[$ぎ-2] #,##0.00" Case Is = Chr$(163) ' British Pound symbol Target.NumberFormat = "[$ﾂ｣-809]#,##0.00" Case Else 'do nothing - no special symbol in A# End Select ExitSheetChange: On Error GoTo 0 ' clear error trapping Exit Sub LeaveChange: Resume ExitSheetChange End Sub "Yendorian" wrote: In EXCEL 2003, i am transferring data from Sheet1 to Sheet2. The data includes one of the three currency signs ($,ぎ,ﾂ｣) in column 1 each time. Can anyone help with a macro that will read the currency sign and then apply that currency sign and format to other cells in the same row (C,D,E)- the same process being repeated each time a new row of data is added. e.g. A B C D E F 1 $ 25.00 $ 67.05 $ 98.00 $ 102.85 67.00 2 ﾂ｣ 34.00 ﾂ｣ 57.64 ﾂ｣ 87.00 ﾂ｣ 111.45 54.00 3 $ 198.00 $ 58.97 $ 25.00 $ 68.30 258.00 4 ぎ 62.57 ぎ 85.21 ぎ 58.00 ぎ 98.35 5.00 5 ﾂ｣ 7.54 ﾂ｣ 1.50 ﾂ｣ 69.00 ﾂ｣ 0.65 4.65 etc Many thanks for all the advice given - this is a steep but fascinating learning curve! Yendorian |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dollar/Euro/Pound Excel macro
The code below should do as you've requested. I've altered it so that
hopefully it will be easier for you to maintain/alter, if your worksheet layout changes by using actual column letter identifiers instead of column numbers. A tad slower, but easier to understand in the long run. You'll note that where we need just a column number later on, the definitions are like "BH1", with full cell address. When asking for the column number, the row address of the cell is irrelevant, so X = Range("BH1").Column and X = Range("BH99").Column will return the same result, the column number for column BH. The code has also been modified to test to see if the changed cell(s) are in any of the 3 areas you've defined. It has been further modified to examine Target (a Range, and a Range can be 1 cell or many) to get down to the individual cell level. This allows you to cut and paste larger sections into the sheet with proper results based on the location of the paste action you make. I've removed all error trapping, so that if something odd is going on, you'll get notified with a program error message and you can press the [Debug] button to see exactly what line in the code generated the error. I don't think that's going to happen unless you have worksheet or workbook protection activated. As before, this code goes into the worksheet code module for the sheet that you want it to work with. That leads to a short discussion of VBA and Excel VBA in particular (especially in light of your "Pity ordinary BASIC is not used now ..." statement) : In Excel there are 4 general locations that you can put code: there is one Workbook code module that is used to handle events at the workbook level such as _Open() _BeforePrint() _BeforeSave() and others. There is only one of these areas per workbook, but each has its own. There is one Worksheet code module for each worksheet in the workbook. The code in those is used primarily to handle worksheet level events such as _Change() and _SelectionChange() - the code for each only works with that sheet. If you wish for multiple sheets to act in the same way, easiest to create one sheet with the code and then copy that sheet as many times as you need. So the Worksheet_Change() event for Sheet1 is different, to the system, than is the Worksheet_Change() event for any other sheet - even though the routines may have the same names and exactly the same code within them. There is a code module associated with any UserForms you may add to the workbook. These operate under the same rules as apply to Worksheet code modules. Finally there are 'general purpose' Modules - there can be none, one or many of them. Their code is generally available to any other code in the workbook, although access to individual routines or functions within them can be restricted by the programmer. It is in these types of modules that Macros created with the 'Record New Macro' feature are stored. As for having moved on past 'ordinary' BASIC (a language unfortunately maligned by 'real' programmers in days past - and one which I often used to do rapid design with prior to laboriously coding up 'real' programs in a variety of assembly languages in those very same days) ... well, you have to consider the new name: VBA = Visual Basic for Applications. The Visual part lets us know that it's designed to work within the GUI/Windows world rather than being locked into a simple character oriented environment such as MS-DOS (or TRS-DOS or C/PM, et al). The 'for Applications' portion tells us that the core of the language has been extended to include a library that allows you to communicate directly with the Application, so in VBA for Excel, the Excel objects such as Workbook, Worksheet, Range, etc. are made readily available without having to resort to #Includes or setting up Tools | References to libraries to get the code to run properly right away. The up side, for you, is that with a good background in BASIC, you've got a good background in the core functions of all dialects of VBA - be it for Excel, Access, Word [don't look at that one, it'll make you crazy - I think the Word object model designers are now all occupying individual rubber rooms <g] or one of the other VBA enabled programs available as Visio or even Outlook. The learning curve is toward learning the 'object model' for the particular Application, and usually a search in the application's VB Editor (not in the main application window) Help for "object model" will reveal it. And recording macros to do what you want to code will often give you revelations into what objects you need to address and what methods (actions) and properties (appearance/behavior) you need to use to accomplish the task - you can then use basic BASIC structures such as IF...THEN and FOR...NEXT to automate the processes and make them more robust. Private Sub Worksheet_Change(ByVal Target As Range) 'should work on even multiple cell changes 'you may change these Const Values to 'adjust for changes in the worksheet layout 'use no column number, just the column ID letter(s) here Const CurrencyTypeColumn = "BH" ' where $, ぎ and ﾂ｣ symbols are 'next two define what columns (inclusive) will change 'format to match symbol in CurrencyTypeColumn on a row 'we just use these to find a column number, so any row number 'will do fine, and using row 1 is consistent. Const FirstSymChgColumn = "BI1" Const LastSymChgColumn = "BQ1" Const FirstAlwaysBritPound = "AT1" Const LastAlwaysBritPound = "BG1" Const FirstAlwaysEuro = "BR1" Const LastAlwaysEuro = "BZ1" 'this variable will represent any individual 'cell within the entire range that is 'Target' 'it may be a single cell, it could be all cells 'on the sheet, or any size group in between Dim anyCell As Range For Each anyCell In Target ' may be 1 or many cells If anyCell.Column = Range(FirstSymChgColumn).Column _ And anyCell.Column <= Range(LastSymChgColumn).Column Then 'it is within columns BH:BQ, inclusive Select Case Range(CurrencyTypeColumn & anyCell.Row) Case Is = Chr$(36) ' U.S. dollar anyCell.NumberFormat = "$#,##0.00" Case Is = Chr$(128) ' Euros anyCell.NumberFormat = "[$ぎ-2] #,##0.00" Case Is = Chr$(163) ' British Pound anyCell.NumberFormat = "[$ﾂ｣-809]#,##0.00" Case Else 'do nothing to change format in anyCell End Select 'falls through and looks for/at next anyCell ElseIf anyCell.Column = Range(FirstAlwaysBritPound).Column _ And anyCell.Column <= Range(LastAlwaysBritPound).Column Then 'is within AT:BG, inclusive anyCell.NumberFormat = "[$ﾂ｣-809]#,##0.00" 'falls through and looks for/at next anyCell ElseIf anyCell.Column = Range(FirstAlwaysEuro).Column _ And anyCell.Column <= Range(LastAlwaysEuro).Column Then 'is within BR:BZ, inclusive anyCell.NumberFormat = "[$ぎ-2] #,##0.00" 'falls through and looks for/at next anyCell Else 'do absolutely nothing! End If Next End Sub "Yendorian" wrote: Hi JL, Thanks for your reply and work. this is exactly what I need. I tried the macro in a new workbook and it worked excellently except that it returned what seem like random signs in the cells after (E) instead of leaving the original format (Number). (It was as if it was not reading the OR part of the 'IF Target.Column <3 Or Target.Column 5' line). I then incorporated it into the prog I am working on - and nothing happened ! So I am doing something wrong somewhere. Here is how I altered your macro: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 61 Or Target.Column 69 Then 'not in A(1) to BH(60) or in BR(70) onwards Exit Sub End If On Error GoTo LeaveChange 'currency code (ﾂ｣,$,ぎ) is in column BH Select Case Range("BH" & Target.Row) Case Is = Chr$(36) ' U.S. dollar symbol Target.NumberFormat = "$#,##0.00" Case Is = Chr$(128) ' Euro symbol Target.NumberFormat = "[$ぎ-2] #,##0.00" Case Is = Chr$(163) ' British Pound symbol Target.NumberFormat = "[$ﾂ｣-809]#,##0.00" Case Else 'do nothing - no special symbol in A# End Select ExitSheetChange: On Error GoTo 0 ' clear error trapping Exit Sub LeaveChange: Resume ExitSheetChange End Sub The currency code is now in column BH (60) and the cells I want to change are from BI (61) to BQ (69) inclusive. Could I be a pain and ask how to add that, in as well as the above macro, figures in cells AT (46) to BG (59) inclusive should be in ﾂ｣ sterling format and those in BR (70) to BZ (78) inclusive should always have the ぎ euro sign. I think I have got the column numbers correct. I've only been into VBA for a few weeks but explanations like yours are extremely helpful. (Pity ordinary BASIC is not used now - I had almost mastered that!!) Thanks once again "JLatham" wrote: Select the worksheet where you want this automatic formatting to take place in columns C, D and E. Right-Click on the sheet's name tab, then choose [View Code]. Copy and paste the code below into the code module that is presented to you in the VB Editor. What this does is detect any change in value in columns C, D or E on that sheet, and then test to see if one of the special characters is 1st character in column A on that sheet, and if so, sets formatting for the cell that changed value accordingly. If this isn't what you desired, let me know. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 3 Or Target.Column 5 Then 'not in C, D or E Exit Sub End If On Error GoTo LeaveChange Select Case Range("A" & Target.Row) Case Is = Chr$(36) ' U.S. dollar symbol Target.NumberFormat = "$#,##0.00" Case Is = Chr$(128) ' Euro symbol Target.NumberFormat = "[$ぎ-2] #,##0.00" Case Is = Chr$(163) ' British Pound symbol Target.NumberFormat = "[$ﾂ｣-809]#,##0.00" Case Else 'do nothing - no special symbol in A# End Select ExitSheetChange: On Error GoTo 0 ' clear error trapping Exit Sub LeaveChange: Resume ExitSheetChange End Sub "Yendorian" wrote: In EXCEL 2003, i am transferring data from Sheet1 to Sheet2. The data includes one of the three currency signs ($,ぎ,ﾂ｣) in column 1 each time. Can anyone help with a macro that will read the currency sign and then apply that currency sign and format to other cells in the same row (C,D,E)- the same process being repeated each time a new row of data is added. e.g. A B C D E F 1 $ 25.00 $ 67.05 $ 98.00 $ 102.85 67.00 2 ﾂ｣ 34.00 ﾂ｣ 57.64 ﾂ｣ 87.00 ﾂ｣ 111.45 54.00 3 $ 198.00 $ 58.97 $ 25.00 $ 68.30 258.00 4 ぎ 62.57 ぎ 85.21 ぎ 58.00 ぎ 98.35 5.00 5 ﾂ｣ 7.54 ﾂ｣ 1.50 ﾂ｣ 69.00 ﾂ｣ 0.65 4.65 etc Many thanks for all the advice given - this is a steep but fascinating learning curve! Yendorian |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dollar/Euro/Pound Excel macro
Hi,
The latest code works brilliantly when I input the data manually into the respective cells. My problem now is that the data is in fact being transferred 'en bloc' from Sheet 1 to Sheet 2 (where the new macro is found) by means of a macro and does not respond to the new macro. If I enter numbers in the respective cells by typing in and pressing ENTER, the specific coding is applied. But ideally, I need ithe coding to be applied automatically when the nuimbers arrive in the cells <without the need for touching Sheet 2, if that's possible. I don't know if you can help again but it's so near to what I'm looking for! Thanks in anticipation Yendorian "JLatham" wrote: The code below should do as you've requested. I've altered it so that hopefully it will be easier for you to maintain/alter, if your worksheet layout changes by using actual column letter identifiers instead of column numbers. A tad slower, but easier to understand in the long run. You'll note that where we need just a column number later on, the definitions are like "BH1", with full cell address. When asking for the column number, the row address of the cell is irrelevant, so X = Range("BH1").Column and X = Range("BH99").Column will return the same result, the column number for column BH. The code has also been modified to test to see if the changed cell(s) are in any of the 3 areas you've defined. It has been further modified to examine Target (a Range, and a Range can be 1 cell or many) to get down to the individual cell level. This allows you to cut and paste larger sections into the sheet with proper results based on the location of the paste action you make. I've removed all error trapping, so that if something odd is going on, you'll get notified with a program error message and you can press the [Debug] button to see exactly what line in the code generated the error. I don't think that's going to happen unless you have worksheet or workbook protection activated. As before, this code goes into the worksheet code module for the sheet that you want it to work with. That leads to a short discussion of VBA and Excel VBA in particular (especially in light of your "Pity ordinary BASIC is not used now ..." statement) : In Excel there are 4 general locations that you can put code: there is one Workbook code module that is used to handle events at the workbook level such as _Open() _BeforePrint() _BeforeSave() and others. There is only one of these areas per workbook, but each has its own. There is one Worksheet code module for each worksheet in the workbook. The code in those is used primarily to handle worksheet level events such as _Change() and _SelectionChange() - the code for each only works with that sheet. If you wish for multiple sheets to act in the same way, easiest to create one sheet with the code and then copy that sheet as many times as you need. So the Worksheet_Change() event for Sheet1 is different, to the system, than is the Worksheet_Change() event for any other sheet - even though the routines may have the same names and exactly the same code within them. There is a code module associated with any UserForms you may add to the workbook. These operate under the same rules as apply to Worksheet code modules. Finally there are 'general purpose' Modules - there can be none, one or many of them. Their code is generally available to any other code in the workbook, although access to individual routines or functions within them can be restricted by the programmer. It is in these types of modules that Macros created with the 'Record New Macro' feature are stored. As for having moved on past 'ordinary' BASIC (a language unfortunately maligned by 'real' programmers in days past - and one which I often used to do rapid design with prior to laboriously coding up 'real' programs in a variety of assembly languages in those very same days) ... well, you have to consider the new name: VBA = Visual Basic for Applications. The Visual part lets us know that it's designed to work within the GUI/Windows world rather than being locked into a simple character oriented environment such as MS-DOS (or TRS-DOS or C/PM, et al). The 'for Applications' portion tells us that the core of the language has been extended to include a library that allows you to communicate directly with the Application, so in VBA for Excel, the Excel objects such as Workbook, Worksheet, Range, etc. are made readily available without having to resort to #Includes or setting up Tools | References to libraries to get the code to run properly right away. The up side, for you, is that with a good background in BASIC, you've got a good background in the core functions of all dialects of VBA - be it for Excel, Access, Word [don't look at that one, it'll make you crazy - I think the Word object model designers are now all occupying individual rubber rooms <g] or one of the other VBA enabled programs available as Visio or even Outlook. The learning curve is toward learning the 'object model' for the particular Application, and usually a search in the application's VB Editor (not in the main application window) Help for "object model" will reveal it. And recording macros to do what you want to code will often give you revelations into what objects you need to address and what methods (actions) and properties (appearance/behavior) you need to use to accomplish the task - you can then use basic BASIC structures such as IF...THEN and FOR...NEXT to automate the processes and make them more robust. Private Sub Worksheet_Change(ByVal Target As Range) 'should work on even multiple cell changes 'you may change these Const Values to 'adjust for changes in the worksheet layout 'use no column number, just the column ID letter(s) here Const CurrencyTypeColumn = "BH" ' where $, ぎ and ﾂ｣ symbols are 'next two define what columns (inclusive) will change 'format to match symbol in CurrencyTypeColumn on a row 'we just use these to find a column number, so any row number 'will do fine, and using row 1 is consistent. Const FirstSymChgColumn = "BI1" Const LastSymChgColumn = "BQ1" Const FirstAlwaysBritPound = "AT1" Const LastAlwaysBritPound = "BG1" Const FirstAlwaysEuro = "BR1" Const LastAlwaysEuro = "BZ1" 'this variable will represent any individual 'cell within the entire range that is 'Target' 'it may be a single cell, it could be all cells 'on the sheet, or any size group in between Dim anyCell As Range For Each anyCell In Target ' may be 1 or many cells If anyCell.Column = Range(FirstSymChgColumn).Column _ And anyCell.Column <= Range(LastSymChgColumn).Column Then 'it is within columns BH:BQ, inclusive Select Case Range(CurrencyTypeColumn & anyCell.Row) Case Is = Chr$(36) ' U.S. dollar anyCell.NumberFormat = "$#,##0.00" Case Is = Chr$(128) ' Euros anyCell.NumberFormat = "[$ぎ-2] #,##0.00" Case Is = Chr$(163) ' British Pound anyCell.NumberFormat = "[$ﾂ｣-809]#,##0.00" Case Else 'do nothing to change format in anyCell End Select 'falls through and looks for/at next anyCell ElseIf anyCell.Column = Range(FirstAlwaysBritPound).Column _ And anyCell.Column <= Range(LastAlwaysBritPound).Column Then 'is within AT:BG, inclusive anyCell.NumberFormat = "[$ﾂ｣-809]#,##0.00" 'falls through and looks for/at next anyCell ElseIf anyCell.Column = Range(FirstAlwaysEuro).Column _ And anyCell.Column <= Range(LastAlwaysEuro).Column Then 'is within BR:BZ, inclusive anyCell.NumberFormat = "[$ぎ-2] #,##0.00" 'falls through and looks for/at next anyCell Else 'do absolutely nothing! End If Next End Sub "Yendorian" wrote: Hi JL, Thanks for your reply and work. this is exactly what I need. I tried the macro in a new workbook and it worked excellently except that it returned what seem like random signs in the cells after (E) instead of leaving the original format (Number). (It was as if it was not reading the OR part of the 'IF Target.Column <3 Or Target.Column 5' line). I then incorporated it into the prog I am working on - and nothing happened ! So I am doing something wrong somewhere. Here is how I altered your macro: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 61 Or Target.Column 69 Then 'not in A(1) to BH(60) or in BR(70) onwards Exit Sub End If On Error GoTo LeaveChange 'currency code (ﾂ｣,$,ぎ) is in column BH Select Case Range("BH" & Target.Row) Case Is = Chr$(36) ' U.S. dollar symbol Target.NumberFormat = "$#,##0.00" Case Is = Chr$(128) ' Euro symbol Target.NumberFormat = "[$ぎ-2] #,##0.00" Case Is = Chr$(163) ' British Pound symbol Target.NumberFormat = "[$ﾂ｣-809]#,##0.00" Case Else 'do nothing - no special symbol in A# End Select ExitSheetChange: On Error GoTo 0 ' clear error trapping Exit Sub LeaveChange: Resume ExitSheetChange End Sub The currency code is now in column BH (60) and the cells I want to change are from BI (61) to BQ (69) inclusive. Could I be a pain and ask how to add that, in as well as the above macro, figures in cells AT (46) to BG (59) inclusive should be in ﾂ｣ sterling format and those in BR (70) to BZ (78) inclusive should always have the ぎ euro sign. I think I have got the column numbers correct. I've only been into VBA for a few weeks but explanations like yours are extremely helpful. (Pity ordinary BASIC is not used now - I had almost mastered that!!) Thanks once again "JLatham" wrote: Select the worksheet where you want this automatic formatting to take place in columns C, D and E. Right-Click on the sheet's name tab, then choose [View Code]. Copy and paste the code below into the code module that is presented to you in the VB Editor. What this does is detect any change in value in columns C, D or E on that sheet, and then test to see if one of the special characters is 1st character in column A on that sheet, and if so, sets formatting for the cell that changed value accordingly. If this isn't what you desired, let me know. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 3 Or Target.Column 5 Then 'not in C, D or E Exit Sub End If On Error GoTo LeaveChange Select Case Range("A" & Target.Row) Case Is = Chr$(36) ' U.S. dollar symbol Target.NumberFormat = "$#,##0.00" Case Is = Chr$(128) ' Euro symbol Target.NumberFormat = "[$ぎ-2] #,##0.00" Case Is = Chr$(163) ' British Pound symbol Target.NumberFormat = "[$ﾂ｣-809]#,##0.00" Case Else 'do nothing - no special symbol in A# End Select ExitSheetChange: On Error GoTo 0 ' clear error trapping Exit Sub LeaveChange: Resume ExitSheetChange End Sub "Yendorian" wrote: In EXCEL 2003, i am transferring data from Sheet1 to Sheet2. The data includes one of the three currency signs ($,ぎ,ﾂ｣) in column 1 each time. Can anyone help with a macro that will read the currency sign and then apply that currency sign and format to other cells in the same row (C,D,E)- the same process being repeated each time a new row of data is added. e.g. A B C D E F 1 $ 25.00 $ 67.05 $ 98.00 $ 102.85 67.00 2 ﾂ｣ 34.00 ﾂ｣ 57.64 ﾂ｣ 87.00 ﾂ｣ 111.45 54.00 3 $ 198.00 $ 58.97 $ 25.00 $ 68.30 258.00 4 ぎ 62.57 ぎ 85.21 ぎ 58.00 ぎ 98.35 5.00 5 ﾂ｣ 7.54 ﾂ｣ 1.50 ﾂ｣ 69.00 ﾂ｣ 0.65 4.65 etc Many thanks for all the advice given - this is a steep but fascinating learning curve! Yendorian |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dollar/Euro/Pound Excel macro
First, leave the other code in the workbook/worksheet code. That will deal
with any changes you make manually on the sheet or when something happens on the sheet to fire its _Change() event. Add the following Private Function to the code module where you're have the macro that is moving data onto that special sheet (I'll call it CurrencySheet here). After copying this code into the regular code module, add a single line to call this function to the code segment that's doing the data move after the move is performed: ForceCurrencyFormatting That'll call the function which will brute-force the format changes. If there are several code modules (whole modules, not just Sub/Functions within a single module), then change 'Private Function' to 'Public Function' so that you don't have to make multiple copies of it. Private Function ForceCurrencyFormatting() As Variant 'input: none required 'output: none - formatting on targetSheetName is altered 'Errors: can error if targetSheetName sheet is protected. 'Comments: This does not fire the sheet's _Change() event. ' so no need to .DisableEvents ' Const targetSheetName = "CurrencySheet" ' CHANGE as required Const CurrencyTypeColumn = "BH" ' where $, ぎ and ﾂ｣ symbols are 'next two define what columns (inclusive) will change 'format to match symbol in CurrencyTypeColumn on a row 'we just use these to find a column number, so any row number 'will do fine, and using row 1 is consistent. '*** for brute force added, MUST use row 1 in the address 'or larger number that is first row that data can be in 'if you have headers in row 1 or more rows. 'the row number MUST be the same for all - change the 'following constant to reflect the number of digits in 'the addresses: if they become as BI10 or BI20, change to 2 'if they become as BI100 or BI999, change to 3, etc. Const numDigitsInColumnAddresses = 1 Const FirstSymChgColumn = "BI1" Const LastSymChgColumn = "BQ1" 'these two declare columns that are always to be in Brit.Pound format Const FirstAlwaysBritPound = "AT1" Const LastAlwaysBritPound = "BG1" 'these two declare columns that are always to be in Euros format Const FirstAlwaysEuro = "BR1" Const LastAlwaysEuro = "BZ1" 'declare a variable to use to reference the Currency Sheet Dim WS As Worksheet 'declare a variable to represent multiple cells Dim largeRange As Range 'this variable will represent any individual 'cell within the entire range that is 'largeRange' Dim anyCell As Range 'to make things easy (for me) a variable to build up an address in Dim anyAddress As String 'finally, to hold the Row# of the LastCell on the sheet Dim lastCellRow As Long Set WS = Worksheets(targetSheetName) lastCellRow = Cells.SpecialCells(xlCellTypeLastCell).Row 'brute force format updates anyAddress = FirstSymChgColumn & ":" & _ Left(LastSymChgColumn, Len(LastSymChgColumn) - _ numDigitsInColumnAddresses) & lastCellRow Set largeRange = WS.Range(anyAddress) For Each anyCell In largeRange 'it is within columns BH:BQ, by definition If IsNumeric(anyCell) Then Select Case Range(CurrencyTypeColumn & anyCell.Row) Case Is = Chr$(36) ' U.S. dollar anyCell.NumberFormat = "$#,##0.00" Case Is = Chr$(128) ' Euros anyCell.NumberFormat = "[$ぎ-2] #,##0.00" Case Is = Chr$(163) ' British Pound anyCell.NumberFormat = "[$ﾂ｣-809]#,##0.00" Case Else 'do nothing to change format in anyCell End Select End If Next anyAddress = FirstAlwaysBritPound & ":" & _ Left(LastAlwaysBritPound, Len(LastAlwaysBritPound) - _ numDigitsInColumnAddresses) & lastCellRow Set largeRange = WS.Range(anyAddress) For Each anyCell In largeRange 'it is within columns AT:BG, by definition If IsNumeric(anyCell) Then anyCell.NumberFormat = "[$ﾂ｣-809]#,##0.00" End If Next anyAddress = FirstAlwaysEuro & ":" & _ Left(LastAlwaysEuro, Len(LastAlwaysEuro) - _ numDigitsInColumnAddresses) & lastCellRow Set largeRange = WS.Range(anyAddress) For Each anyCell In largeRange 'it is within columns AT:BG, by definition If IsNumeric(anyCell) Then anyCell.NumberFormat = "[$ぎ-2] #,##0.00" End If Next End Function "Yendorian" wrote: Hi, The latest code works brilliantly when I input the data manually into the respective cells. My problem now is that the data is in fact being transferred 'en bloc' from Sheet 1 to Sheet 2 (where the new macro is found) by means of a macro and does not respond to the new macro. If I enter numbers in the respective cells by typing in and pressing ENTER, the specific coding is applied. But ideally, I need ithe coding to be applied automatically when the nuimbers arrive in the cells <without the need for touching Sheet 2, if that's possible. I don't know if you can help again but it's so near to what I'm looking for! Thanks in anticipation Yendorian "JLatham" wrote: The code below should do as you've requested. I've altered it so that hopefully it will be easier for you to maintain/alter, if your worksheet layout changes by using actual column letter identifiers instead of column numbers. A tad slower, but easier to understand in the long run. You'll note that where we need just a column number later on, the definitions are like "BH1", with full cell address. When asking for the column number, the row address of the cell is irrelevant, so X = Range("BH1").Column and X = Range("BH99").Column will return the same result, the column number for column BH. The code has also been modified to test to see if the changed cell(s) are in any of the 3 areas you've defined. It has been further modified to examine Target (a Range, and a Range can be 1 cell or many) to get down to the individual cell level. This allows you to cut and paste larger sections into the sheet with proper results based on the location of the paste action you make. I've removed all error trapping, so that if something odd is going on, you'll get notified with a program error message and you can press the [Debug] button to see exactly what line in the code generated the error. I don't think that's going to happen unless you have worksheet or workbook protection activated. As before, this code goes into the worksheet code module for the sheet that you want it to work with. That leads to a short discussion of VBA and Excel VBA in particular (especially in light of your "Pity ordinary BASIC is not used now ..." statement) : In Excel there are 4 general locations that you can put code: there is one Workbook code module that is used to handle events at the workbook level such as _Open() _BeforePrint() _BeforeSave() and others. There is only one of these areas per workbook, but each has its own. There is one Worksheet code module for each worksheet in the workbook. The code in those is used primarily to handle worksheet level events such as _Change() and _SelectionChange() - the code for each only works with that sheet. If you wish for multiple sheets to act in the same way, easiest to create one sheet with the code and then copy that sheet as many times as you need. So the Worksheet_Change() event for Sheet1 is different, to the system, than is the Worksheet_Change() event for any other sheet - even though the routines may have the same names and exactly the same code within them. There is a code module associated with any UserForms you may add to the workbook. These operate under the same rules as apply to Worksheet code modules. Finally there are 'general purpose' Modules - there can be none, one or many of them. Their code is generally available to any other code in the workbook, although access to individual routines or functions within them can be restricted by the programmer. It is in these types of modules that Macros created with the 'Record New Macro' feature are stored. As for having moved on past 'ordinary' BASIC (a language unfortunately maligned by 'real' programmers in days past - and one which I often used to do rapid design with prior to laboriously coding up 'real' programs in a variety of assembly languages in those very same days) ... well, you have to consider the new name: VBA = Visual Basic for Applications. The Visual part lets us know that it's designed to work within the GUI/Windows world rather than being locked into a simple character oriented environment such as MS-DOS (or TRS-DOS or C/PM, et al). The 'for Applications' portion tells us that the core of the language has been extended to include a library that allows you to communicate directly with the Application, so in VBA for Excel, the Excel objects such as Workbook, Worksheet, Range, etc. are made readily available without having to resort to #Includes or setting up Tools | References to libraries to get the code to run properly right away. The up side, for you, is that with a good background in BASIC, you've got a good background in the core functions of all dialects of VBA - be it for Excel, Access, Word [don't look at that one, it'll make you crazy - I think the Word object model designers are now all occupying individual rubber rooms <g] or one of the other VBA enabled programs available as Visio or even Outlook. The learning curve is toward learning the 'object model' for the particular Application, and usually a search in the application's VB Editor (not in the main application window) Help for "object model" will reveal it. And recording macros to do what you want to code will often give you revelations into what objects you need to address and what methods (actions) and properties (appearance/behavior) you need to use to accomplish the task - you can then use basic BASIC structures such as IF...THEN and FOR...NEXT to automate the processes and make them more robust. Private Sub Worksheet_Change(ByVal Target As Range) 'should work on even multiple cell changes 'you may change these Const Values to 'adjust for changes in the worksheet layout 'use no column number, just the column ID letter(s) here Const CurrencyTypeColumn = "BH" ' where $, ぎ and ﾂ｣ symbols are 'next two define what columns (inclusive) will change 'format to match symbol in CurrencyTypeColumn on a row 'we just use these to find a column number, so any row number 'will do fine, and using row 1 is consistent. Const FirstSymChgColumn = "BI1" Const LastSymChgColumn = "BQ1" Const FirstAlwaysBritPound = "AT1" Const LastAlwaysBritPound = "BG1" Const FirstAlwaysEuro = "BR1" Const LastAlwaysEuro = "BZ1" 'this variable will represent any individual 'cell within the entire range that is 'Target' 'it may be a single cell, it could be all cells 'on the sheet, or any size group in between Dim anyCell As Range For Each anyCell In Target ' may be 1 or many cells If anyCell.Column = Range(FirstSymChgColumn).Column _ And anyCell.Column <= Range(LastSymChgColumn).Column Then 'it is within columns BH:BQ, inclusive Select Case Range(CurrencyTypeColumn & anyCell.Row) Case Is = Chr$(36) ' U.S. dollar anyCell.NumberFormat = "$#,##0.00" Case Is = Chr$(128) ' Euros anyCell.NumberFormat = "[$ぎ-2] #,##0.00" Case Is = Chr$(163) ' British Pound anyCell.NumberFormat = "[$ﾂ｣-809]#,##0.00" Case Else 'do nothing to change format in anyCell End Select 'falls through and looks for/at next anyCell ElseIf anyCell.Column = Range(FirstAlwaysBritPound).Column _ And anyCell.Column <= Range(LastAlwaysBritPound).Column Then 'is within AT:BG, inclusive anyCell.NumberFormat = "[$ﾂ｣-809]#,##0.00" 'falls through and looks for/at next anyCell ElseIf anyCell.Column = Range(FirstAlwaysEuro).Column _ And anyCell.Column <= Range(LastAlwaysEuro).Column Then 'is within BR:BZ, inclusive anyCell.NumberFormat = "[$ぎ-2] #,##0.00" 'falls through and looks for/at next anyCell Else 'do absolutely nothing! End If Next End Sub "Yendorian" wrote: Hi JL, Thanks for your reply and work. this is exactly what I need. I tried the macro in a new workbook and it worked excellently except that it returned what seem like random signs in the cells after (E) instead of leaving the original format (Number). (It was as if it was not reading the OR part of the 'IF Target.Column <3 Or Target.Column 5' line). I then incorporated it into the prog I am working on - and nothing happened ! So I am doing something wrong somewhere. Here is how I altered your macro: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 61 Or Target.Column 69 Then 'not in A(1) to BH(60) or in BR(70) onwards Exit Sub End If On Error GoTo LeaveChange 'currency code (ﾂ｣,$,ぎ) is in column BH Select Case Range("BH" & Target.Row) Case Is = Chr$(36) ' U.S. dollar symbol Target.NumberFormat = "$#,##0.00" Case Is = Chr$(128) ' Euro symbol Target.NumberFormat = "[$ぎ-2] #,##0.00" Case Is = Chr$(163) ' British Pound symbol Target.NumberFormat = "[$ﾂ｣-809]#,##0.00" Case Else 'do nothing - no special symbol in A# End Select ExitSheetChange: On Error GoTo 0 ' clear error trapping Exit Sub LeaveChange: Resume ExitSheetChange End Sub The currency code is now in column BH (60) and the cells I want to change are from BI (61) to BQ (69) inclusive. Could I be a pain and ask how to add that, in as well as the above macro, figures in cells AT (46) to BG (59) inclusive should be in ﾂ｣ sterling format and those in BR (70) to BZ (78) inclusive should always have the ぎ euro sign. I think I have got the column numbers correct. I've only been into VBA for a few weeks but explanations like yours are extremely helpful. (Pity ordinary BASIC is not used now - I had almost mastered that!!) Thanks once again "JLatham" wrote: Select the worksheet where you want this automatic formatting to take place in columns C, D and E. Right-Click on the sheet's name tab, then choose [View Code]. Copy and paste the code below into the code module that is presented to you in the VB Editor. What this does is detect any change in value in columns C, D or E on that sheet, and then test to see if one of the special characters is 1st character in column A on that sheet, and if so, sets formatting for the cell that changed value accordingly. If this isn't what you desired, let me know. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 3 Or Target.Column 5 Then 'not in C, D or E Exit Sub End If On Error GoTo LeaveChange Select Case Range("A" & Target.Row) Case Is = Chr$(36) ' U.S. dollar symbol Target.NumberFormat = "$#,##0.00" Case Is = Chr$(128) ' Euro symbol Target.NumberFormat = "[$ぎ-2] #,##0.00" Case Is = Chr$(163) ' British Pound symbol Target.NumberFormat = "[$ﾂ｣-809]#,##0.00" Case Else 'do nothing - no special symbol in A# End Select ExitSheetChange: On Error GoTo 0 ' clear error trapping Exit Sub LeaveChange: Resume ExitSheetChange End Sub "Yendorian" wrote: In EXCEL 2003, i am transferring data from Sheet1 to Sheet2. The data includes one of the three currency signs ($,ぎ,ﾂ｣) in column 1 each time. Can anyone help with a macro that will read the currency sign and then apply that currency sign and format to other cells in the same row (C,D,E)- the same process being repeated each time a new row of data is added. e.g. A B C D E F 1 $ 25.00 $ 67.05 $ 98.00 $ 102.85 67.00 2 ﾂ｣ 34.00 ﾂ｣ 57.64 ﾂ｣ 87.00 ﾂ｣ 111.45 54.00 3 $ 198.00 $ 58.97 $ 25.00 $ 68.30 258.00 4 ぎ 62.57 ぎ 85.21 ぎ 58.00 ぎ 98.35 5.00 5 ﾂ｣ 7.54 ﾂ｣ 1.50 ﾂ｣ 69.00 ﾂ｣ 0.65 4.65 etc Many thanks for all the advice given - this is a steep but fascinating learning curve! Yendorian |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dollar/Euro/Pound Excel macro
I intentionally added this since it is not a mandatory deal, and didn't want
to confuse things earlier. You can leave the code I've provided so far as it was presented and it should work just fine for you. Only problem with it is that if you change the worksheet name involved, or the columns involved, you have to remember to make changes in 2 places in the workbook. That's a maintenance issue. To make maintenance easier, you might want to consider the following: Move the definitions of the Const values used in both the Worksheet_Change() code and the Private Function ...() that I provided into the declarations section of a regular code module, such as the one where you put the Private Function. Declare them as Public Const and then you can DELETE their declarations as Const within both of the routines. So if things change in the future, you can make changes in just the one location and both processes will have the new definitions they need. The declarations section of a regular (or any) code module is that area above the first Sub or Function declaration in that module. The declarations (without comments) would look like this: Public Const targetSheetName = "CurrencySheet" ' CHANGE as required Public Const CurrencyTypeColumn = "BH" ' where $, ぎ and ﾂ｣ symbols are Public Const numDigitsInColumnAddresses = 1 Public Const FirstSymChgColumn = "BI1" Public Const LastSymChgColumn = "BQ1" Public Const FirstAlwaysBritPound = "AT1" Public Const LastAlwaysBritPound = "BG1" Public Const FirstAlwaysEuro = "BR1" Public Const LastAlwaysEuro = "BZ1" If you've got any questions about the finer points of any of this that you think may not be appropriate/beneficial to ask here in the forum, you can ask me via eMail through (remove spaces) HelpFrom @ jlathamsite.com "Yendorian" wrote: Hi, The latest code works brilliantly when I input the data manually into the respective cells. My problem now is that the data is in fact being transferred 'en bloc' from Sheet 1 to Sheet 2 (where the new macro is found) by means of a macro and does not respond to the new macro. If I enter numbers in the respective cells by typing in and pressing ENTER, the specific coding is applied. But ideally, I need ithe coding to be applied automatically when the nuimbers arrive in the cells <without the need for touching Sheet 2, if that's possible. I don't know if you can help again but it's so near to what I'm looking for! Thanks in anticipation Yendorian "JLatham" wrote: The code below should do as you've requested. I've altered it so that hopefully it will be easier for you to maintain/alter, if your worksheet layout changes by using actual column letter identifiers instead of column numbers. A tad slower, but easier to understand in the long run. You'll note that where we need just a column number later on, the definitions are like "BH1", with full cell address. When asking for the column number, the row address of the cell is irrelevant, so X = Range("BH1").Column and X = Range("BH99").Column will return the same result, the column number for column BH. The code has also been modified to test to see if the changed cell(s) are in any of the 3 areas you've defined. It has been further modified to examine Target (a Range, and a Range can be 1 cell or many) to get down to the individual cell level. This allows you to cut and paste larger sections into the sheet with proper results based on the location of the paste action you make. I've removed all error trapping, so that if something odd is going on, you'll get notified with a program error message and you can press the [Debug] button to see exactly what line in the code generated the error. I don't think that's going to happen unless you have worksheet or workbook protection activated. As before, this code goes into the worksheet code module for the sheet that you want it to work with. That leads to a short discussion of VBA and Excel VBA in particular (especially in light of your "Pity ordinary BASIC is not used now ..." statement) : In Excel there are 4 general locations that you can put code: there is one Workbook code module that is used to handle events at the workbook level such as _Open() _BeforePrint() _BeforeSave() and others. There is only one of these areas per workbook, but each has its own. There is one Worksheet code module for each worksheet in the workbook. The code in those is used primarily to handle worksheet level events such as _Change() and _SelectionChange() - the code for each only works with that sheet. If you wish for multiple sheets to act in the same way, easiest to create one sheet with the code and then copy that sheet as many times as you need. So the Worksheet_Change() event for Sheet1 is different, to the system, than is the Worksheet_Change() event for any other sheet - even though the routines may have the same names and exactly the same code within them. There is a code module associated with any UserForms you may add to the workbook. These operate under the same rules as apply to Worksheet code modules. Finally there are 'general purpose' Modules - there can be none, one or many of them. Their code is generally available to any other code in the workbook, although access to individual routines or functions within them can be restricted by the programmer. It is in these types of modules that Macros created with the 'Record New Macro' feature are stored. As for having moved on past 'ordinary' BASIC (a language unfortunately maligned by 'real' programmers in days past - and one which I often used to do rapid design with prior to laboriously coding up 'real' programs in a variety of assembly languages in those very same days) ... well, you have to consider the new name: VBA = Visual Basic for Applications. The Visual part lets us know that it's designed to work within the GUI/Windows world rather than being locked into a simple character oriented environment such as MS-DOS (or TRS-DOS or C/PM, et al). The 'for Applications' portion tells us that the core of the language has been extended to include a library that allows you to communicate directly with the Application, so in VBA for Excel, the Excel objects such as Workbook, Worksheet, Range, etc. are made readily available without having to resort to #Includes or setting up Tools | References to libraries to get the code to run properly right away. The up side, for you, is that with a good background in BASIC, you've got a good background in the core functions of all dialects of VBA - be it for Excel, Access, Word [don't look at that one, it'll make you crazy - I think the Word object model designers are now all occupying individual rubber rooms <g] or one of the other VBA enabled programs available as Visio or even Outlook. The learning curve is toward learning the 'object model' for the particular Application, and usually a search in the application's VB Editor (not in the main application window) Help for "object model" will reveal it. And recording macros to do what you want to code will often give you revelations into what objects you need to address and what methods (actions) and properties (appearance/behavior) you need to use to accomplish the task - you can then use basic BASIC structures such as IF...THEN and FOR...NEXT to automate the processes and make them more robust. Private Sub Worksheet_Change(ByVal Target As Range) 'should work on even multiple cell changes 'you may change these Const Values to 'adjust for changes in the worksheet layout 'use no column number, just the column ID letter(s) here Const CurrencyTypeColumn = "BH" ' where $, ぎ and ﾂ｣ symbols are 'next two define what columns (inclusive) will change 'format to match symbol in CurrencyTypeColumn on a row 'we just use these to find a column number, so any row number 'will do fine, and using row 1 is consistent. Const FirstSymChgColumn = "BI1" Const LastSymChgColumn = "BQ1" Const FirstAlwaysBritPound = "AT1" Const LastAlwaysBritPound = "BG1" Const FirstAlwaysEuro = "BR1" Const LastAlwaysEuro = "BZ1" 'this variable will represent any individual 'cell within the entire range that is 'Target' 'it may be a single cell, it could be all cells 'on the sheet, or any size group in between Dim anyCell As Range For Each anyCell In Target ' may be 1 or many cells If anyCell.Column = Range(FirstSymChgColumn).Column _ And anyCell.Column <= Range(LastSymChgColumn).Column Then 'it is within columns BH:BQ, inclusive Select Case Range(CurrencyTypeColumn & anyCell.Row) Case Is = Chr$(36) ' U.S. dollar anyCell.NumberFormat = "$#,##0.00" Case Is = Chr$(128) ' Euros anyCell.NumberFormat = "[$ぎ-2] #,##0.00" Case Is = Chr$(163) ' British Pound anyCell.NumberFormat = "[$ﾂ｣-809]#,##0.00" Case Else 'do nothing to change format in anyCell End Select 'falls through and looks for/at next anyCell ElseIf anyCell.Column = Range(FirstAlwaysBritPound).Column _ And anyCell.Column <= Range(LastAlwaysBritPound).Column Then 'is within AT:BG, inclusive anyCell.NumberFormat = "[$ﾂ｣-809]#,##0.00" 'falls through and looks for/at next anyCell ElseIf anyCell.Column = Range(FirstAlwaysEuro).Column _ And anyCell.Column <= Range(LastAlwaysEuro).Column Then 'is within BR:BZ, inclusive anyCell.NumberFormat = "[$ぎ-2] #,##0.00" 'falls through and looks for/at next anyCell Else 'do absolutely nothing! End If Next End Sub "Yendorian" wrote: Hi JL, Thanks for your reply and work. this is exactly what I need. I tried the macro in a new workbook and it worked excellently except that it returned what seem like random signs in the cells after (E) instead of leaving the original format (Number). (It was as if it was not reading the OR part of the 'IF Target.Column <3 Or Target.Column 5' line). I then incorporated it into the prog I am working on - and nothing happened ! So I am doing something wrong somewhere. Here is how I altered your macro: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 61 Or Target.Column 69 Then 'not in A(1) to BH(60) or in BR(70) onwards Exit Sub End If On Error GoTo LeaveChange 'currency code (ﾂ｣,$,ぎ) is in column BH Select Case Range("BH" & Target.Row) Case Is = Chr$(36) ' U.S. dollar symbol Target.NumberFormat = "$#,##0.00" Case Is = Chr$(128) ' Euro symbol Target.NumberFormat = "[$ぎ-2] #,##0.00" Case Is = Chr$(163) ' British Pound symbol Target.NumberFormat = "[$ﾂ｣-809]#,##0.00" Case Else 'do nothing - no special symbol in A# End Select ExitSheetChange: On Error GoTo 0 ' clear error trapping Exit Sub LeaveChange: Resume ExitSheetChange End Sub The currency code is now in column BH (60) and the cells I want to change are from BI (61) to BQ (69) inclusive. Could I be a pain and ask how to add that, in as well as the above macro, figures in cells AT (46) to BG (59) inclusive should be in ﾂ｣ sterling format and those in BR (70) to BZ (78) inclusive should always have the ぎ euro sign. I think I have got the column numbers correct. I've only been into VBA for a few weeks but explanations like yours are extremely helpful. (Pity ordinary BASIC is not used now - I had almost mastered that!!) Thanks once again "JLatham" wrote: Select the worksheet where you want this automatic formatting to take place in columns C, D and E. Right-Click on the sheet's name tab, then choose [View Code]. Copy and paste the code below into the code module that is presented to you in the VB Editor. What this does is detect any change in value in columns C, D or E on that sheet, and then test to see if one of the special characters is 1st character in column A on that sheet, and if so, sets formatting for the cell that changed value accordingly. If this isn't what you desired, let me know. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 3 Or Target.Column 5 Then 'not in C, D or E Exit Sub End If On Error GoTo LeaveChange Select Case Range("A" & Target.Row) Case Is = Chr$(36) ' U.S. dollar symbol Target.NumberFormat = "$#,##0.00" Case Is = Chr$(128) ' Euro symbol Target.NumberFormat = "[$ぎ-2] #,##0.00" Case Is = Chr$(163) ' British Pound symbol Target.NumberFormat = "[$ﾂ｣-809]#,##0.00" Case Else 'do nothing - no special symbol in A# End Select ExitSheetChange: On Error GoTo 0 ' clear error trapping Exit Sub LeaveChange: Resume ExitSheetChange End Sub "Yendorian" wrote: In EXCEL 2003, i am transferring data from Sheet1 to Sheet2. The data includes one of the three currency signs ($,ぎ,ﾂ｣) in column 1 each time. Can anyone help with a macro that will read the currency sign and then apply that currency sign and format to other cells in the same row (C,D,E)- the same process being repeated each time a new row of data is added. e.g. A B C D E F 1 $ 25.00 $ 67.05 $ 98.00 $ 102.85 67.00 2 ﾂ｣ 34.00 ﾂ｣ 57.64 ﾂ｣ 87.00 ﾂ｣ 111.45 54.00 3 $ 198.00 $ 58.97 $ 25.00 $ 68.30 258.00 4 ぎ 62.57 ぎ 85.21 ぎ 58.00 ぎ 98.35 5.00 5 ﾂ｣ 7.54 ﾂ｣ 1.50 ﾂ｣ 69.00 ﾂ｣ 0.65 4.65 etc Many thanks for all the advice given - this is a steep but fascinating learning curve! Yendorian |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dollar / Euro sign | Excel Worksheet Functions | |||
| Problem extracting Euro and Pound symbol | Excel Discussion (Misc queries) | |||
| Euro & British pound symbol issue | Excel Discussion (Misc queries) | |||
| How can I change dollar sybbol to euro symbol? | New Users to Excel | |||
| Can I disply Euro and Dollar Formats in the same spreadsheet | Excel Discussion (Misc queries) | |||
Hybrid Mode
