How to Convert blanks to "-"

[VeeBa]

Hello Experts! Help pls. I have below code in my workbook which formats the numbers in my pivot table to 2 decimal places (and no decimal for UK region) and turns zeros into "-". How can i add a line item to change also blanks (not zeros) with "-"? Can i incorporate it below or requires another line item of code? Any help is appreciated. Thanks!

With pvttblBusiness.PivotFields("Summary of Expenses")
        .Orientation = xlDataField
        If UCase(RegionSelected) = "UK" And UCase(BusinessName) = "Eng" Then
            [B].NumberFormat = "#,##0; -#,##0; -"[/B]
        Else
           [B] .NumberFormat = "#,##0.00; -#,##0.00; -"[/B]
        End If
        .Function = xlSum
End With[FONT=Helvetica][SIZE=3][COLOR=#1d2129]
[/COLOR][/SIZE][/FONT]

 

[JackDanIce]

Try:

With pvttblBusiness.PivotFields("Summary of Expenses")
        .Orientation = xlDataField
        .NumberFormat = IIf(UCase(RegionSelected) = "UK" And UCase(BusinessName) = "ENG", "#,##0;-#,##0;-;", "#,##0.00;-#,##0.00;-;")
        .Function = xlSum
End With

 

[VeeBa]

Try:

With pvttblBusiness.PivotFields("Summary of Expenses")
        .Orientation = xlDataField
        .NumberFormat = IIf(UCase(RegionSelected) = "UK" And UCase(BusinessName) = "ENG", "#,##0;-#,##0;-;", "#,##0.00;-#,##0.00;-;")
        .Function = xlSum
End With

Hi JackDanIce! Thanks for the response. But it's not working I can still see blanks in my data fields..

 

[JackDanIce]

I'm not too sure I'm afraid, but does seem unusual quirk.

Applying number formatting "#,##0;-#,##0;-" to cell A1 then using values of -1, 0 and 1 I can get the cell to show zero as a hyphen. I next confirmed this using the same values in cell A2 and running code:

cells(2,1).NumberFormat = "#,##0;-#,##0;-"

Which also displays 0 as hyphen.

Maybe try recording a macro and checking the pivot table values do format as you require, then adapt that code to your macro?

 

[VeeBa]

I'm not too sure I'm afraid, but does seem unusual quirk.

Applying number formatting "#,##0;-#,##0;-" to cell A1 then using values of -1, 0 and 1 I can get the cell to show zero as a hyphen. I next confirmed this using the same values in cell A2 and running code:

cells(2,1).NumberFormat = "#,##0;-#,##0;-"

Which also displays 0 as hyphen.

Maybe try recording a macro and checking the pivot table values do format as you require, then adapt that code to your macro?

Sorry I think my question wasn't clear..Actually, its not the zeros that I am trying to replace with "-". I am trying to replace blank cells with "-". I tried recording but it only gave out numberformat = nullstring code..when I tried to incorporate in my codes, it is debugging.. it's not working.. any suggestions?

 

[JackDanIce]

This does replace zero with "-"

:#,##0;-#,##0;-

Whether I use that directly to define the cell format or in VBA as argument to .NumberFormat, tested to verify

 

[VeeBa]

This does replace zero with "-"

:#,##0;-#,##0;-

Whether I use that directly to define the cell format or in VBA as argument to .NumberFormat, tested to verify

Hi JackDanIce - Sorry but my question is how to replace blanks (as in empty cells not zeros) in my pivot fields with "-"?

 

[SyedUsman]

ActiveSheet.PivotTables("Summary of Expenses").NullString = "-"

 

[VeeBa]

ActiveSheet.PivotTables("Summary of Expenses").NullString = "-"

Still not working. I am encountering debug:

Run time error 438: Object doesn't support this property or method

Any advice?

 

[VeeBa]

This does replace zero with "-"

:#,##0;-#,##0;-

Whether I use that directly to define the cell format or in VBA as argument to .NumberFormat, tested to verify

Hi Jack! Can you help on this buddy? I am willing to share my workbook so you can try on your end..I've tried a lot of codes but nothing is working