Using Excel VBA to set number format

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!

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_ ;_ € * "" - ""??_ ;_ @_ "

Leave a Comment