title: VBA Runtime error '1004'. AutoFilter method of Range class failed
body: <p>Everytime I execute my Macro (below) to search for a string that is entered into my search box, I get a Runtime error '1004'. AutoFilter method of Range class failed. I've tried looking for an answer to this problem on here for a while but nothing seems to provide me with a solution. </p>
  
  <p>When I hit 'Debug' it highlights the below section of the code.</p>
  
  <pre><code>    DataRange.AutoFilter _
      Field:=myField, _
      Criteria1:=SearchString, _
      Operator:=xlAnd
  </code></pre>
  
  <p>I realise that this isn't much info but any help would be extremely appreciated. </p>
  
  <p>P.S. I'm a complete novice with VBA and copied the code off a website that did a step by step guide to creating a search filter in Excel.</p>
  
  <pre><code>Sub SearchBox()
  
  Dim myButton As OptionButton
  Dim SearchString As String
  Dim ButtonName As String
  Dim sht As Worksheet
  Dim myField As Long
  Dim DataRange As Range
  Dim mySearch As Variant
  
  'Load Sheet into A Variable
    Set sht = ActiveSheet
  
  'Unfilter Data (if necessary)
    On Error Resume Next
      sht.ShowAllData
    On Error GoTo 0
  
  'Filtered Data Range (include column heading cells)
    Set DataRange = sht.Range("A5:Z40000") 'Cell Range
    'Set DataRange = sht.ListObjects("Table1").Range 'Table
  
  'Retrieve User's Search Input
    mySearch = sht.Shapes("UserSearch").TextFrame.Characters.Text 'Control Form
    'mySearch = sht.OLEObjects("UserSearch").Object.Text 'ActiveX Control
    'mySearch = sht.Range("A1").Value 'Cell Input
  
  'Determine if user is searching for number or text
    If IsNumeric(mySearch) = True Then
      SearchString = "=" &amp; mySearch
    Else
      SearchString = "=*" &amp; mySearch &amp; "*"
    End If
  
  'Loop Through Option Buttons
    For Each myButton In sht.OptionButtons
      If myButton.Value = 1 Then
        ButtonName = myButton.Text
        Exit For
      End If
    Next myButton
  
  'Determine Filter Field
    On Error GoTo HeadingNotFound
      myField = Application.WorksheetFunction.Match(ButtonName, DataRange.Rows(1), 0)
    On Error GoTo 0
  
  'Filter Data
    DataRange.AutoFilter _
      Field:=myField, _
      Criteria1:=SearchString, _
      Operator:=xlAnd
  
  'Clear Search Field
    sht.Shapes("UserSearch").TextFrame.Characters.Text = "" 'Control Form
    'sht.OLEObjects("UserSearch").Object.Text = "" 'ActiveX Control
    'sht.Range("A1").Value = "" 'Cell Input
  
  Exit Sub
  
  'ERROR HANDLERS
  HeadingNotFound:
    MsgBox "The column heading [" &amp; ButtonName &amp; "] was not found in cells " &amp; DataRange.Rows(1).Address &amp; ". " &amp; _
      vbNewLine &amp; "Please check for possible typos.", vbCritical, "Header Name Not Found!"
  
  End Sub
  </code></pre>