if range is Nothing Exit sub, userform, specialcells

Question

Goal: Search range for cells containing errors, if found a modeless useform shows and allows you to change the sell to "yes", "no", or "Review later". If no cells with errors are found in range, msgbox appears to let you know, hide userform and exit sub.

Problem: I can not get the If range is nothing hide userform and exit sub to work properly. Whenever I reach the point where all the cell errors are dealt with I get a 1004 error on the range "no cells were found".

Sub UserformYes_no_review()
Dim Custchk As CustomListCheck
Set Custchk = VBA.UserForms.Add(CustomListCheck.Name)

With New CustomListCheck
    Set CheckRange = Sheets("Sheet1").Range("A1:N2000").SpecialCells(xlCellTypeFormulas, xlErrors)
    If CheckRange Is Nothing Then
      MsgBox "All items have been accounted for"
      CustomListCheck.Hide
      Exit Sub
    Else
        For Each Cell In CheckRange
            Cell.Select
            If VarType(ActiveCell.Value) = vbError Then
                Custchk.Show vbModeless
            End If

        Next Cell
    End If
End With

End Sub

Private Sub CommandButton1_Click()
ActiveCell.Value = "Yes"
Call UserformYes_no
End Sub

Private Sub CommandButton2_Click()
ActiveCell.Value = "No"
Call UserformYes_no  
End Sub

Private Sub CommandButton3_Click()
ActiveCell.Value = "Review Later"
Call UserformYes_no
End Sub

I have looked through a wealth of Stackoverflow pages and tried all the solutions that i could find and nothing is working. As a side note, I used a userform over a msgbox as I needed this to be modeless.

Thanks, @siddharth_Rout I have padded the Set Check Range line with the above error handling which gets me passed the 1004 error I was receiving. Now I am getting 424 error "object required" on the "If CheckRange Is Nothing Then". Were you implying to pad the whole macro with this? — Ryan S

Siddharth Rout Siddharth Rout · Accepted Answer · 2019-12-19T17:22:53

When ever you are working with SpecialCells, use error handling.

Change

Set CheckRange = Sheets("Sheet1").Range("A1:N2000").SpecialCells(xlCellTypeFormulas, xlErrors)

to

On Error Resume Next
Set CheckRange = Sheets("Sheet1").Range("A1:N2000").SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

if range is Nothing Exit sub, userform, specialcells

2 Answers