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.
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
' 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 129
Downloads 48
CodeID
DB ID
