Convert_Dash_to_Zero

Convert cells with dashes to zeros.
Quick code needed to reset sheet copied from a damaged workbook, while having their format set to 'Accounting'.
This caused cells to show '-' instead of their actual zeros, so this small code was used to reset them back.

CodeFunctionName
What is this?

Public

Tested

Original Work
Function Convert_Dash_to_Zero(StartFromCell, ForRows, ForColumns, Optional InSheet = "This", Optional InWB = "This")
    ' Convert any cell having only - to 0
    ' Quick fix, needed because when we moved data from previous copy of "Data" sheet, the 0 formatted as Accounting have been converted to -
    ' returns number of cells that have been modified
    Rett = 0
    If InWB = "This" Then InWB = ThisWorkbook.Name
    If InSheet = "This" Then InSheet = Workbooks(InWB).ActiveSheet.Name
    StartFrom = StartFromCell ' "M1"
    X1 = 1
    Max1 = ForRows ' 4320 ' Rows
    Max2 = ForColumns ' 533 ' columns
    Do Until X1 > Max1
        X2 = 0
        Do Until X2 > Max2
            ValCurr = Workbooks(inWB).Worksheets(InSheet).Range(StartFromCell).Offset(X1, X2).Value ' SHDB.Range("M1").Offset(X1, X2).Value
            If ValCurr = " -    " Then
                Workbooks(inWB).Worksheets(InSheet).Range(StartFromCell).Offset(X1, X2).Value = 0 '            SHDB.Range("M1").Offset(X1, X2).Value = 0
                Rett = Rett + 1
            End If
            DoEvents
            X2 = X2 + 1
        Loop
        DoEvents
        X1 = X1 + 1
        DoEvents
    Loop
    Convert_Dash_to_Zero = Rett
End Function

StartFromCell, ForRows, ForColumns, Optional InSheet = "This", Optional InWB = "This"

Views 90

Downloads 23

CodeID
DB ID