Examine and change format for each cell containing zero

[ou812]

Hi all,

I have about 30 spreadsheets I have to process every week. For each cell in these spreadsheet that contain a 0, I have to manually change the format to currency without the $ sign. If a cell has a value other than o, I need to leave it in the current format. As you can imagine, doing this manually is quite time consuming. Is there a script that can examine each cell and make the changes when needed?

 

[fhqwgads]

If a cell has a value other than o, I need to leave it in the current format

what is the current format? custom number formats allow you to to define the format for cells containing 0
if it's formatted as currency you can use $#,##0.00;-$#,##0.00;0.00;@ as the custom format which will display cells with 0 as 0.00

 

[ou812]

Some cells are numbers with or without decimal places while others are percentages with 2 decimal places. What I want to end up with is a dash in place of the cells containing 0, or 0.00, or 0.00%.

 

[fhqwgads]

what about a find/replace on your whole workbook to replace 0 with -
or do you need to keep the 0 value in your cells?

 

[ou812]

I really should keep it as a 0, but the replace is an option of last resort I guess.

 

[fhqwgads]

aw shucks
sorry i wasnt much help

 

[DENomad]

Hi ou812,

Try this

Sub myTest()


Dim cell As Range, rng As Range
Dim lastrow As Long


lastrow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row


Set rng = ThisWorkbook.Sheets("Sheet1").Range("A1", "A" & lastrow) 'You can extend the range here >>> i.e. ThisWorkbook.Sheets("Sheet1").Range("A1", "AA" & lastrow)


Application.ScreenUpdating = False


With rng
    For Each cell In rng
    
        ' This will remove the format cell 'Text' format
        ' You can remove this code if the cell will never be formatted as 'Text'
        '// Delete if not needed ------------------------------------------------------
            cell.TextToColumns Destination:=cell, _
            DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 1), TrailingMinusNumbers:=True
         '\\ ---------------------------------------------------------------------------
        
        If cell = 0 Then
            cell.NumberFormat = "$0.00"
        End If
                    
    Next cell
End With


Application.ScreenUpdating = True


End Sub

 

[ou812]

Thanks a lot. This should work great. If I wanted to change the formatting to accounting without the $, what would that NumberFormat pattern look like?

 

[DENomad]

Hi ou812,

Use the following:

cell.NumberFormat = "_-* #,##0.00_-;-* #,##0.00_-;_-* ""-""??_-;_-@_-"

 

[ou812]

Perfect! I can't read it but it worked exactly as needed.

Thank you again!