title: Unable to give reference for VLOOKUP on special cells
body: <p>I am trying to you vlookup on the specials where we have N/A in prior column but not able to succeed as cell reference for vlookup is creating or problem and i tried to research it and fix it but couldn't get the right one. Would be helpful if any of you can help me to correct it</p>
  
  <p>N/A can be in any of the cells in column K and once i filter column k with N/A's here and need to vlookup in column L using below formula on the special cells which are filtered but i am facing challenge with giving vlookup reference cell for which i need to your assistance as N/A can be in K2/K16/K20/K50/K80 </p>
  
  <p>=IFERROR(VLOOKUP($D16,'BP Scoping'!A:B,2,0),D16)</p>
  
  <pre><code>'Second vlookup not working
  With Sheets("Sheet4")
      Dim LRW As Long
      LRW = .Range("A" &amp; Rows.Count).End(xlUp).Row
  
      .Range("A1").AutoFilter Field:=11, Criteria1:="#N/A"
  
       'need to check for right cells which is missing
       'One way tried to use this
       .Range(.Range("L2"), Cells(LRW, "L")).SpecialCells(xlCellTypeVisible).Formula = "=iferror(VLOOKUP($D2,'BP Scoping'!A:B,2,0),D2)"
  </code></pre>
  
  <p>Another way I tried to use this:</p>
  
  <pre><code>     .Range("L2:L" &amp; LRW).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=iferror(VLOOKUP($D2,'BP Scoping'!A:B,2,0),D2)"
  </code></pre>