CODEWITHSUNDEEP
vbaexcel
user3147731
user3147731

Reputation: 111

apply user-defined format in excel 2007 vba

The excel 2007 format i am using is: #,##0.00,,,_);[Red](#,##0.00,,,);"-"

if is is possitive, i want the number to display in billions; if negative, in billions and in parentahis; if missing, "-".

It works fine with excel 2007. But when i tried to apply the format in vba, it did not work.

Please use the following numbers as example:

-11467478
224785.66
-5579046
1904770.9
-14916968

The data type i used is variant. shall i use long?

my initial code is something like: 

......
with worksheet
'cells(1,1) would be any of the above numbers
  .Cells(1, 1).NumberFormat = "#,##0.00,,,_);[Red](#,##0.00,,,);" - ""
end with
.....

I got an erros message run-time error 13, type mismatch

I even tried to decompose the format. but it still did not work.

i am quite new to vba. could anyone help me? ......

Upvotes: 0

Views: 150

Answers (2)

Cool Blue
Cool Blue

Reputation: 6476

This will also work, the dash is one of the characters that don't need to be escaped...

"#,##0.00,,,_);[Red](#,##0.00,,,);-"

Upvotes: 2

PatricK
PatricK

Reputation: 6433

You need to use double " for the minus sign. I tested your values and I think it should be:

.NumberFormat = "#,##0.00,,,_);[Red](#,##0.00,,,);""-"""

customNumberFormat

Upvotes: 1

Related Questions