Dim sname As String
Dim kom As Integer  '検索する項目
Dim dat             '検索するデ−タ
Sub ki157a()
Sheets("Sheet1").Select
    ki157
End Sub

Sub ki157()

 Application.ScreenUpdating = False
 sname = ActiveSheet.Name
' デ−タ入力
 msg = "検索したい品名を入力して下さい。"
    dat = Application.InputBox(msg, "品名の検索", "", Type:=2)
         kom = 2: ms1$ = "品名"
     
'データ２個検索
     op = 0: data = 0: datb = 0
    data = InStr(1, dat, " or", 1)
    datb = InStr(1, dat, " and", 1)
    If data > 1 Then
       op = 1
       dat1 = Trim(Mid(dat, 1, data - 1))
       dat2 = Trim(Mid(dat, data + 3))
    End If
    If datb > 1 Then
       op = 2
       dat1 = Trim(Mid(dat, 1, datb - 1))
       dat2 = Trim(Mid(dat, datb + 4))
    End If
'
'デ−タ検索
      Application.Worksheets(sname).Activate
      Range("a1").Select
    If op = 1 Then
      Selection.AutoFilter Field:=kom, Criteria1:=dat1, _
      Operator:=xlOr, Criteria2:=dat2
    ElseIf op = 2 Then
      Selection.AutoFilter Field:=kom, Criteria1:=dat1, _
      Operator:=xlAnd, Criteria2:=dat2
    Else
      Selection.AutoFilter Field:=kom, Criteria1:=dat
    End If
'
  If ActiveSheet.Buttons.Count = 1 Then
    ActiveSheet.Buttons.Select
        nam = Selection.Name
    ActiveSheet.Buttons(nam).Select
        Selection.Delete
  End If
    
    ActiveSheet.Buttons.Add(340.5, 1.5, 31.5, 14.25).Select
        Selection.OnAction = "解除"
        Selection.Characters.Text = "解除"
    Range("A1").Select
   
End Sub

Sub 解除()
 '  オ−トフィルタ−解除
     Application.Worksheets(sname).Activate
       ActiveSheet.AutoFilterMode = False
       ActiveSheet.Buttons.Select
       nam = Selection.Name
       
       ActiveSheet.Buttons(nam).Select
        Selection.Delete
      ActiveSheet.Buttons.Add(340.5, 1.5, 31.5, 14.25).Select
    Selection.Characters.Text = "検索"
    Selection.OnAction = "ki157"
    Range("A1").Select
End Sub