VBA maco to change currency is formattign as number?/

  • Hi ALL,

    i found this code that changes all cells formatted as currency in a workbook to what is in a select list value (G4).I am experiencing the problem that when the select list changes from one value to the next there is activity but the cells that are supposed to be changed form say R1234.00 to

    £1234.00 are not changing. When I right click on a cell that is supposed to have been changed I see they are now formatted as Number?

    This is the VBA I am using in Excel 2016.

    I hope someone can help me here as searched and searched but getting no-where...

  • Quote

    the cells that are supposed to be changed

    What are the cells that are supposed to change format?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi Mumps ,

    Thank you for your reply. I am attempting to change all the cells in the whole workbook that are formatted as Currency.

    I used the code I posted in the workbook that the coder supplied and it works fine however when I place the same code in my own workbook it formats the cells as Number.

    I have attached two workbooks. One is called FARES.xlsb which are extracts of my whole workbook which is 28mb. The other is called ChangeStyle.xlsb which is the demo workbook in which the code works fine.

    Many thanks for taking the time to help, we newbies appreciate it...

    Files

    • workbooks.zip

      (169.72 kB, downloaded 29 times, last: )
  • Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in E7.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi Mumps,

    Many thanks. It does not work and now I am wondering if my system is somehow corrupted.

    I found this code below and it worked perfectly and when I tried to save the workbook Excel crashed. After restarting I am now receiving error 1004 "Application-or object-defined error" each time I attempt to change the currency..(on lines, 13,15,17 and 19 depending on the currency I am changing)

  • I tested the code on Sheet1 in the file you posted using E7 as the target cell and it worked exactly as you requested. I'm not sure what you are trying to do as you now appear to be looping through each sheet for some reason.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi again,

    Apologies for the confusion... As per my original post I am trying to change each cell in a work book based on the selects currency. The values in range 'A'came from the coder who provided a demo file which did work but not in my own workbook. Instead of the code reformatting all cells in the workbook that were formatted as 'Currency - No symbol, 2 decimals' to the selected currency and format it was reformatting to 'Number' format.

    The code I posted recently did what I wanted until excel crashed and now I am back to square one :(

  • In the code you posted, you refer to G4 as the target cell and H5 as the reference cell. Looking at your FARES file, those cells are blank in all the sheets and are formatted as "General". In which specific sheets do you want to format the cells and what is the target cell and reference cell? I used E7 on Sheet1 as the target cell because that cell contains the currency codes. Please clarify in detail.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • The cell 'G4' (now in the sheet called 'Temp') is the select list value ('GBP','R' etc) that I am using to choose a new currency. 'H5' is a currently a formatted cell, which I understood by the remarked sentence just above that line in the code, that would be searched for throughout the work book and replaced. As I say, it was working until Excel crashed. I have attached a new Fares.zip file with my latest efforts. The tab called temp is where I make the currency selection and the changes should take effect on tabs 'Temp' and 'Converted fares' only and any other sheet within the work book I will eventually be using this code on. Sheet 'Base fares' are conditionally formatted and so will not be altered which is correct. Column 'A' in Temp is just so I can see if there is any immediate change and those figures have no relevance other than that.

    Once I have working code the select list will go into the main workbook's 'General Assumptions' sheet and so effect the changes through all 30 other sheets...

    As you will see in the "Converted Fares" tab, the last time the code worked before it crashed it did change the symbol in rows D14:AK16. (I was busy changing the base format on the rest of the sheet when it crashed) . These tabs are just extracts of my whole book which is 28Mb and there are many sheets I will have to go through and change the base formatting to once I know what I am doing with this test file..

    I appreciate your patience Mamps..

    Files

    • FARES.zip

      (155.33 kB, downloaded 26 times, last: )
  • Before testing it on all the sheets, try this code in the code module for the "Temp" sheet and see how it works.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • It works on Temp but only for currencies 'GBP' and 'EUR'. 'R' and 'USD' returned application-defined or object-defined error.

    Cell d6:d9 did not change but I suspect is because they are not defined in the code?

  • The code is currently designed to work on the Temp sheet. Did it work for you?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • I managed to get the 'USD' currency OK...there was an underscore before the semi-colon ("[$$-409]#,##0.00_ ;) which i removed and that is now OK.

    The 'ZAR' is still an issue.. I have changed the code for one that is working in the other workbooks i sent but no go.. It seems to be the 'R' that is causing the problem.. just to test I replaced the 'R' with a '$' and it changed without any error albeit the symbol was wrong for that currency...

    I will have bash at it again tomorrow...

    Many hanks once again