vba help - Number format for blank cell want dash

[Mallesh23]

Hi Team,

Need your help in applying format, if there are blank add dash.

Below attempted code which only covers positive and negative nos. not blank cells

Sub test()
Range("A2:A6").NumberFormat = "#,#.00"
End Sub

Below is a table with expected output in Column C.

Book10
	A	B	C
1	Raw Data		Expected
2	5000		5,000.00
3	100		100.00
4	-4000		-4,000.00
5			-
6	18000		18,000.00
Sheet1

Thanks
mg

 

[phillip87]

for what purpose are you using VBA rather than a formula?

 

[phillip87]

=IF(C2="","-",C2)

This would give you the outcome you are after although I do not know what else it is you have running or why.

 

[Mallesh23]

Hi Philip,

I am using VBA. for formatting.


Thanks
Mg

 

[phillip87]

If you just format the cells to the desired format then you can just run an "IF" formula and keep everything very simple and easy to modify for yourself.

Below is just using the standard "general" format in the worksheet, as soon as the raw data is entered then your expected will auto fill, this will also let you add in helper cells to add or subtract further.

 

[yky]

If you set the cell format to accounting without a symbol, Excel will automatically convert 0 to dash. So, this is another option - convert blank to zero.

 

[Mallesh23]

Hi Yku and Philips,

Thanks for your help, will check and test.

I found below in Custom Format.........

_ * #,##0_ ;_ * -#,##0_ ;_ * "-"??_ ;_ @_


Thanks
mg

 

[RoryA]

Just FYI, a number format can never alter the appearance of an empty cell. It can only change the appearance of actual cell content.

 

[Mallesh23]

Hi Rory,

Custom Format. shows below formating..
_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * "-"??_ ;_ @_


Range("c1:c100").NumberFormat = "_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * ""-""??_ ;_ @_"
But if I use above custom format in VBA , Getting Error Message "Unable to set number format property.

Thanks
mg

 

[RoryA]

You missed a space off the end of the format string:

Range("c1:c100").NumberFormat = "_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * ""-""??_ ;_ @_ "