title: VBA: Autofilter method of Range Class Failed
body: <p>I am getting the error </p>
  
  <blockquote>
    <p>Autofilter method of range class failed</p>
  </blockquote>
  
  <p>on the line</p>
  
  <p><code>RngToSearch.AutoFilter 1, Criteria1:=ProgramNoVal</code></p>
  
  <p>What I am trying to do is filter the data in column C of the Program Main sheet based on <code>ProgramNoVal</code> and then copy the value for the corresponding column K in my Mkting tab at <code>RngDest</code>. </p>
  
  <pre><code>Sub test()
  
  Dim ProgramNoVal
  Dim RngToSearch As Range, Rng As Range, RngDest As Range
  ProgramNoVal = Sheets("Instructions").Range("G12").Value
  
  With Sheets("Mkting")
          Set RngDest = .Range("D1")
  End With
  
  Sheets("ProgramMain").Select
  Set RngToSearch = Range("C1:K30000")
  Set Rng = RngToSearch.Offset(1, 8).Resize(RngToSearch.Rows.Count - 1, 1)
  
  ActiveSheet.AutoFilterMode = False
  RngToSearch.AutoFilter 1, Criteria1:=ProgramNoVal
  Rng.SpecialCells(xlCellTypeVisible).Copy RngDest
  RngToSearch.AutoFilter
  
  End Sub
  </code></pre>
  
  <p>I have tried this a different way, using vlookup. I get a type mismatch for foundRow. The value it is finding is a string. I have tried string, long, range, etc and nothing works. Solutions to either of these pieces of code is appreciated. The code for vlookup is:</p>
  
  <pre><code>Sub test()
  
  Dim ProgramNoVal
  Dim RngToSearch As Range, Rng As Range, RngDest As Range
  Dim foundRow As long
  
  
  With Sheets("Mkting")
     Set RngDest = .Range("D1")
  End With
  
  ProgramNoVal = Sheets("Instructions").Range("G12").Value
  
      Set RngToSearch = Sheets("ProgramMain").Range("C1:K30000")
      foundRow = Sheets("ProgramMain").Application.VLookup(ProgramNoVal, RngToSearch, 9, False)
  
      If Not IsError(foundRow) Then
          RngToSearch.Cells(foundRow, 1).Copy RngDest
      End If
  End Sub
  </code></pre>