how to put a really long formula's result into a cell using application.worksheetfunction-CodePudding

I'm looking to add this formula (and a few others) to a tool I'm creating.

=IFERROR(INDEX('TO Pick'!A:Z,MATCH(Historic!M6,'TO Pick'!E:E,0),13) INDEX(Pick!A:Z,MATCH(Historic!M6,Pick!E:E,0),13),""))

The issue is, I need the data that this formula generates, but not the formula itself (keeping the formula in the cell messes the logic I'm working with in the tool)

To avoid this I plan on instead setting the cell.value = application.worksheetfunction. But I'm struggling to get the formatting correctly. Is there any workarounds for this? Or could someone explain how to organize a formula in this way?

CodePudding user response：

The following code first enters the formula in cell B2, and then it converts the formula into a value. Change the destination cell accordingly.

With Range("B2")
    .Formula = "=IFERROR(INDEX('TO Pick'!A:Z,MATCH(Historic!M6,'TO Pick'!E:E,0),13) INDEX(Pick!A:Z,MATCH(Historic!M6,Pick!E:E,0),13),"""")"
    .Value = .Value
End With