title: Insert the outcome of a formula using vba
body: <p>I want to insert a formula in a range of cells in column P. Afterwards I want the outcome of the formula to be a hard value (so not the formula but the outcome). At the moment I'm placing the formula in the cells, then autofill to last row and then copy column and past special. I want to avoid using select - copy - paste. I was wondering if anyone knows how to do this, I've seen it before but can't find it anymore. </p>
  
  <p>The code I'm using at the moment</p>
  
  <pre><code>Set frCP = Worksheets("Compliance").Range("P" &amp; FirstRowFill &amp; ":P" &amp; LastRowMatchC)
  
  
  With Worksheets("Compliance")
      .Range("P" &amp; FirstRow).Offset(1).FormulaArray = "=IF(IFERROR(INDEX(Input!$A$2:A$" &amp; LastRowInput &amp; ",MATCH(1,SEARCH(TRANSPOSE(Input!$A$2:A$" &amp; LastRowInput &amp; "),O" &amp; FirstRowOffset1 &amp; "),0),0),W" &amp; FirstRowOffset1 &amp; ")=0,""ZZ"",IFERROR(INDEX(Input!$A$2:A$" &amp; LastRowInput &amp; ",MATCH(1,SEARCH(TRANSPOSE(Input!$A$2:A$" &amp; LastRowInput &amp; "),O" &amp; FirstRowOffset1 &amp; "),0),0),W" &amp; FirstRowOffset1 &amp; "))"
      .Range("P" &amp; FirstRow).Offset(1).AutoFill Destination:=frCP
  End With
  
  Columns("P:P").Select
  Range("P88").Activate
  Selection.Copy
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
  
  With Worksheets("Compliance")
      .Range("A" &amp; FirstRow).Rows(ActiveCell.Row).AutoFilter
      .AutoFilter.Sort.SortFields.Clear
      .AutoFilter.Sort.SortFields.Add2 Key:= _
      .Range("P" &amp; FirstRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
          xlSortNormal
  End With
      With ActiveWorkbook.Worksheets("Compliance").AutoFilter.Sort
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
  End With
  </code></pre>