VBA to search or autofilter

[Jagat Pavasia]

Dear sir,

I have Excel Sheet as Image.
I have add VBA code Below :

Private Sub worksheet_change(ByVal Target As Range)

Dim rng As Range
 
  If Target.Count > 1 Then Exit Sub
  Application.EnableEvents = False
  ActiveSheet.Unprotect
  Set rng = Range("B:B,L:L")
  If Not Intersect(Target, rng) Is Nothing Then
    If Target.Value = "**" Then Target.Value = Format(Date, "mm/dd/yyyy")
  End If

  If Not Intersect(Target, Range("A1:U1")) Is Nothing Then
      If Target.Value = "" Then
          ActiveSheet.Range("A3:U3").AutoFilter Field:=Target.Column
      Else
          ActiveSheet.Range("A3:U3").AutoFilter Field:=Target.Column, Operator:=xlFilterValues, Criteria1:=CStr(Target.Value)
      End If
  End If
  ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFiltering:=True
  Application.EnableEvents = True
End Sub

Now all this working as it should, but I want that if I enter "DTC" in E1 then auto filter all match word in E3:E1000,
another example : If I enter "3/1" then auto filter fin E3:E1000

 

[Artik]

E.x.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range

    If Target.CountLarge > 1 Then Exit Sub

    Application.EnableEvents = False

    Me.Unprotect

    Set rng = Me.Range("B:B,L:L")

    If Not Intersect(Target, rng) Is Nothing Then
        If Target.Value = "**" Then
            Target.Value = Date
            Target.NumberFormat = "mm/dd/yyyy"
        End If
    End If

    If Not Intersect(Target, Me.Range("E1")) Is Nothing Then
        If Target.Value = "" Then
            Me.Range("A3:U3").AutoFilter Field:=Target.Column
        Else
            Me.Range("A3:U3").AutoFilter Field:=Target.Column, _
                                         Operator:=xlFilterValues, _
                                         Criteria1:="*" & CStr(Target.Value) & "*"
        End If
    ElseIf Not Intersect(Target, Me.Range("A1:U1")) Is Nothing Then
        If Target.Value = "" Then
            Me.Range("A3:U3").AutoFilter Field:=Target.Column
        Else
            Me.Range("A3:U3").AutoFilter Field:=Target.Column, _
                                         Operator:=xlFilterValues, _
                                         Criteria1:=CStr(Target.Value)
        End If
    End If

    Me.Protect DrawingObjects:=False, Contents:=True, _
               Scenarios:=False, AllowFormattingCells:=True, _
               AllowFiltering:=True

    Application.EnableEvents = True
End Sub

Some comments:
1. Target.Count. Since version 2007, you should use CountLarge to not cause an overflow error.
2. Date entry into cells. Do not pass a date into a cell using the Format function, even if it works for you. Pass the date and format the cell appropriately if needed. Your version:

Target.Value = Format(Date, "mm/dd/yyyy")

caused me to insert text, not a date!
3. If you are looking for a piece of text in the criterion add * characters.
If you add the character * only at the beginning, the autofilter will search for everything that ends with the defined text, if * is at the end - the autofilter will search for everything that begins with the defined text. If * are on both sides, the autofilter will look for everything that contains the defined text.

Artik

 

[Jagat Pavasia]

E.x.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range

    If Target.CountLarge > 1 Then Exit Sub

    Application.EnableEvents = False

    Me.Unprotect

    Set rng = Me.Range("B:B,L:L")

    If Not Intersect(Target, rng) Is Nothing Then
        If Target.Value = "**" Then
            Target.Value = Date
            Target.NumberFormat = "mm/dd/yyyy"
        End If
    End If

    If Not Intersect(Target, Me.Range("E1")) Is Nothing Then
        If Target.Value = "" Then
            Me.Range("A3:U3").AutoFilter Field:=Target.Column
        Else
            Me.Range("A3:U3").AutoFilter Field:=Target.Column, _
                                         Operator:=xlFilterValues, _
                                         Criteria1:="*" & CStr(Target.Value) & "*"
        End If
    ElseIf Not Intersect(Target, Me.Range("A1:U1")) Is Nothing Then
        If Target.Value = "" Then
            Me.Range("A3:U3").AutoFilter Field:=Target.Column
        Else
            Me.Range("A3:U3").AutoFilter Field:=Target.Column, _
                                         Operator:=xlFilterValues, _
                                         Criteria1:=CStr(Target.Value)
        End If
    End If

    Me.Protect DrawingObjects:=False, Contents:=True, _
               Scenarios:=False, AllowFormattingCells:=True, _
               AllowFiltering:=True

    Application.EnableEvents = True
End Sub

Some comments:
1. Target.Count. Since version 2007, you should use CountLarge to not cause an overflow error.
2. Date entry into cells. Do not pass a date into a cell using the Format function, even if it works for you. Pass the date and format the cell appropriately if needed. Your version:

Target.Value = Format(Date, "mm/dd/yyyy")

caused me to insert text, not a date!
3. If you are looking for a piece of text in the criterion add * characters.
If you add the character * only at the beginning, the autofilter will search for everything that ends with the defined text, if * is at the end - the autofilter will search for everything that begins with the defined text. If * are on both sides, the autofilter will look for everything that contains the defined text.

Artik

Thank you so much ARTIK, it is working as I should............