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