Set cell equal to formula after clicking VBA button-CodePudding

I want to set cell E1 equal to a formula after clicking a button. The formula that currently exists in this cell is:

=IFERROR(INDEX(OFFSET('AMP Sheet'!$A:$A,,MATCH("ID",'AMP Sheet'!$1:$1,0)-1),MATCH("*"&B1&"*",OFFSET('AMP Sheet'!$A:$A,,MATCH("Name",'AMP Sheet'!$1:$1,0)-1),0)),"")

Essentially, I have a VBA button that fills down this formula in column E, based on the number of items in column B:

With Sheets("AssetName Sheet")
    .range("E1").AutoFill .range("E1:E" & .Cells(.Rows.Count, "B").End(xlUp).Row)
End With

Instead of just filling down the formula in E1, I want to set E1 equal to the formula and then fill down. I would like store the formula within my VBA button code, rather than in the cell itself.

Here is my attempt at it, but it didn't work.

.range("E1").Value = [IFERROR(INDEX(OFFSET('AMP Sheet'!$A:$A,,MATCH("ID",'AMP Sheet'!$1:$1,0)-1),MATCH("*"&B1&"*",OFFSET('AMP Sheet'!$A:$A,,MATCH("Name",'AMP Sheet'!$1:$1,0)-1),0)),"")]

Thanks!

CodePudding user response：

Like this:

'double-up any " inside the formula string...
Const F As String = "=IFERROR(INDEX(OFFSET('AMP Sheet'!$A:$A,,MATCH(""ID"",'AMP Sheet'!$1:$1,0)-1)," & _
    "MATCH(""*""&B1&""*"",OFFSET('AMP Sheet'!$A:$A,,MATCH(""Name"",'AMP Sheet'!$1:$1,0)-1),0)),"""")"

With Sheets("AssetName Sheet")
    .Range("E1:E" & .Cells(Rows.Count, "E").End(xlUp).Row).Formula = F
End With