display userform if value of textbox not found in filtered data

[redspanna]

Hi all

Can somebody help with this VBA below

All I need is if the value added into the Textbox1 isn't found through the filtered data then display userform4

my code as follows...

Private Sub CommandButton1_Click()



Sheets("Inventory").Select

With ThisWorkbook.Worksheets("Inventory")
        .AutoFilterMode = False
        With Range("B4:H1000")
            .AutoFilter
            If Len(Me.TextBox1.Value) > 0 Then
                .AutoFilter Field:=1, Criteria1:=Me.TextBox1.Value
            End If
            
            
        End With
        
        
UserForm2.TextBox1.Value = ""

UserForm2.Hide
End With
End Sub

 

[Domenic]

The following code uses the SpecialCells method to determine whether there are any filtered records. As such, the defined range had to be changed from a fixed range to a dynamic range.

        With Range("B4:H" & Cells(Rows.Count, "B").End(xlUp).Row)
            .AutoFilter
            If Len(Me.TextBox1.Value) > 0 Then
                .AutoFilter Field:=1, Criteria1:=Me.TextBox1.Value
                On Error Resume Next
                Set filterRange = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
                On Error GoTo 0
                If filterRange Is Nothing Then
                    UserForm4.Show
                    Set filterRange = Nothing
                End If
            End If
        End With

And, of course, you should declare filterRange...

Dim filterRange As Range

Hope this helps!

 

[redspanna]

That's great - thanks a lot

 

[Domenic]

You're very welcome, cheeers!