The number 0 is not displayed in the cell

[Sambecko]

Hi All,
Kindly help,I have a macro that can't display the number 0, Loop through numbers 0 to 9 doesn't work well. Thank you.
#
Private Sub MissingNumber_Click()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet3")

Dim i As Integer
Dim num As Integer
Dim found As Boolean

' Clear previous results
ws.Range("M2:V2").ClearContents
ActiveCell.NumberFormat = "0"

' Loop through numbers 0 to 9
For num = 0 To 9
found = False

' Check if num exists in range A2:J2
For i = 1 To 10 ' A to J columns
If ws.Cells(2, i).Value = num Then
found = True
Exit For
End If
Next i

' If num is not found, place it in the result range M2:V2
If Not found Then
For i = 13 To 22 ' M to V columns
If ws.Cells(2, i).Value = "" Then
ws.Cells(2, i).Value = num
Exit For
End If
Next i
End If
Next num

MsgBox "Missing numbers have been found and placed in range M2:V2.", vbInformation

End Sub

 

[RoryA]

An empty cell will be treated as equivalent to 0. You should test for empty cells specifically - for example:

If ws.Cells(2, i).Value <> "" and ws.Cells(2, i).Value = num Then

 

[Sambecko]

Thank you for contributing to this problem, I have tried but it hasn't worked.

 

[myall_blues]

Where did you put the line suggested by @RoryA. It should go here:

    ' Check if num exists in range A2:J2
        For i = 1 To 10 ' A to J columns
            If ws.Cells(2, i).Value <> "" And ws.Cells(2, i).Value = num Then   '<== line goes here
                found = True
                Exit For
            End If
        Next i

Gives results as below:

As a precaution you could also add these two lines after the 'set' statement.

    Set ws = ThisWorkbook.Sheets("Sheet3")
    ws.Activate                         '<== Add this line
    ActiveWindow.DisplayZeros = True    '<== Add this line