CODEWITHSUNDEEP
excelvbaexcel-formulaworksheet-function
j johns
j johns

Reputation: 39

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

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?

Upvotes: 0

Views: 56

Answers (1)

Domenic
Domenic

Reputation: 8104

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

Upvotes: 2

Related Questions