VBA Selection.NumberFormat "... ?

[Pinaceous]

Good Day Everyone!

I'm running a VBA code that tells the cell in a specific range its NumberFormat.

For Example:

Range("B11:B180").Select
    Selection.NumberFormat = "0000"

My question arises when I'm trying to put ###,###,##0.000 "KG" as the NumberFormat.

For Example:

Range("B11:B180").Select
    Selection.NumberFormat = "###,###,##0.000 "KG""

It comes up totally red on the second code and I cannot figure out why?

I'm thinking it has something to do with the use of the double brackets of " but I don't know how to rewrite this to make sense VBA wise.

Can you help?

Please post, if you can.

Thanks,
pinaceous

 

[Oddball2020]

I'm not familar with using something like "KG" in the numberformat line, but generally in VBA dealing with quotes, as they're apart of the syntax, you may need double or triple quotes, or even & if used in conjunction with a defined variable, before something works.

Is KG a variable?

""KG""

or "" & KG & "" or even """ & KG & """ depending on the VBA code syntax.... good luck!

 

[MARK858]

Try

Range("B11:B180").NumberFormat = "###,###,##0.000 "" Kg"""

 

[Rick Rothstein]

This should also work (I used the shortcut notation for fixed, unchanging ranges, you don't need the extra # signs with the extra comma for that number format and you don't have to worry about the quotes)...

[B11:B180].NumberFormat = "#,##0.000 \K\g"

 

[Pinaceous]

Many thanks!

That did it!

 

[smozgur]

That did it!

@Pinaceous - which one did it?
Could you please mark that post as the solution so it helps future readers faster by indicating that this question has at least one working solution? Thanks.

 

[Oddball2020]

This should also work (I used the shortcut notation for fixed, unchanging ranges, you don't need the extra # signs with the extra comma for that number format and you don't have to worry about the quotes)...

[B11:B180].NumberFormat = "#,##0.000 \K\g"

Ooooh... KG is kilograms? which gets added into the cell after the number? Interesting, never knew!