Autofilter - no cells were found

[CEG]

How can I modify this code so that a message box pops up when the criteria is not found and avoid getting the error "no cells were found"?

Private Sub tbJobNo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 13 Then
        Dim xVal As Variant
        Worksheets("part number").AutoFilterMode = False
        With Worksheets("part number").Range("Job_No")
            .AutoFilter Field:=1, Criteria1:=frmDQT.tbJobNo.Value
            If .Range("Job_No") Is Nothing Then
            Exit Sub
            frmDQT.cboPN.Clear
            For Each xVal In .Columns(2).SpecialCells(xlCellTypeVisible)
                frmDQT.cboPN.AddItem xVal.Value
            Next xVal
            .AutoFilter
        End With
    End If
    ActiveSheet.Rows("3:3").Hidden = True
    tbPartNo = ""
End Sub

Thanks,
CEG

 

[pedie]

Not sure but have u tried adding error handler?

[/FONT]
[FONT=Courier New]Private Sub tbJobNo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 13 Then
        Dim xVal As Variant
        Worksheets("part number").AutoFilterMode = False
        With Worksheets("part number").Range("Job_No")
            .AutoFilter Field:=1, Criteria1:=frmDQT.tbJobNo.Value[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]on error resume next
            If .Range("Job_No") Is Nothing Then[/FONT]
[FONT=Courier New]msgbox "Not found...": Exit Sub[/FONT]
[FONT=Courier New]on error goto 0
            frmDQT.cboPN.Clear
            For Each xVal In .Columns(2).SpecialCells(xlCellTypeVisible)
                frmDQT.cboPN.AddItem xVal.Value
            Next xVal
            .AutoFilter
        End With
    End If
    ActiveSheet.Rows("3:3").Hidden = True
    tbPartNo = ""
End Sub

 

[CEG]

How do I re-write this line so it doesn't error out?

If .Range("Job_No") Is Nothing Then