0

I am trying to set some monetary values in Excel in a specific format used by my department, but it does not accept my specific format. I tested a very simple format which was accepted, so I am 99% sure it is the format that is fighting me.

My code is

ThisWorkbook.Sheets("Calculatie").Range("G2:H1000").NumberFormat = "_ € * #.##0,00_ ;_ € * -#.##0,00_ ;_ € * " - "??_ ;_ @_ "

Expected output has € on the left of the cell and the value alignet on the right (0-values are "€ -"). Instead I recieve an error;

Error 13 during execution: Types don't match (Translated from Dutch, exact wording might be off)

The following code does work;

ThisWorkbook.Sheets("Calculatie").Range("G2:H1000").NumberFormat = "0.00;0.00;0.00;@"

I copied the format from the "custom number notations" menu in Excel, and checking it with the site below and it appears to be correct.

https://www.excelanytime.com/excel/index.php?option=com_content&view=article&id=121:custom-number-formats-date-a-time-formats-in-excel-a-vba-numberformat-property&catid=79&Itemid=475

Thanks in advance for the help!

Rick
  • 53
  • 8
  • 1
    Try `"_ € * #.##0,00_ ;_ € * -#.##0,00_ ;_ € * "" - ""??_ ;_ @_ "` – BigBen Jul 30 '21 at 13:43
  • It's running now! Thats a great start, but for some reason, 56 euros become €56,000 instead of €56,00. I can't see where the additional 0 comes from, but removing a 0 fixes it; ```"_ € * #.##0,0_ ;_ € * -#.##0,0_ ;_ € * "" - ""??_ ;_ @_ "``` – Rick Jul 30 '21 at 13:50

1 Answers1

1

User BigBen helped me along a great way! The problem was a double "" around the "-".

EDIT; I had to change my code. I still had weird issues after my change. The issue was no solved by removing a 0 as I though earlier. Probably due to my Dutch setting, I had to swap decimal points and commas. The code that works for me is;

ThisWorkbook.Sheets("Calculatie").Range("G2:H1000").NumberFormat = "_ € * #,##0.00_ ;_ € * -#,##0.00_ ;_ € * "" - ""??_ ;_ @_ "

Rick
  • 53
  • 8
  • 3
    Based on your edit, can you try using `.NumberFormatLocal`, instead of `.NumberFormat`, with the original format? – BigBen Jul 30 '21 at 18:37
  • I have tested it real quick and it appears that works just as well. Thanks for the additional advice! – Rick Aug 02 '21 at 06:11