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.

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