VBA code to store an array formula

[learningexcel123]

Can someone help me determine the right code to store an array formula into a cell? For example, I can use the below to store a non-array formula but how do I modify it to store an array formula? (i.e. with the curly braces on the outside) Generally, we would have to hold down Control+Shift+Enter to change a formula to an array formula but not sure how to do in VBA.

Sub Update_Header_Formulas()
Dim ws As Worksheet
For Each ws In Sheets(Array("Sheet1"))
ws.Range("E4").Formula = "=IF(ISNA(VLOOKUP($C$3&""*"",'[Mar_costs.xlsx]data'!$A$11:$Z$1239,7,0))=TRUE,"""",VLOOKUP($C$3&""*"",'[Mar_costs.xlsx]data'!$A$11:$Z$1239,7,0))"
Next ws
End Sub

 

[hiker95]

learningexcel123,

Try:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

ws.Range("E4").FormulaArray = "=IF(ISNA(VLOOKUP($C$3&""*"",'[Mar_costs.xlsx]data'!$A$11:$Z$1239,7,0))=TRUE,"""",VLOOKUP($C$3&""*"",'[Mar_costs.xlsx]data'!$A$11:$Z$1239,7,0))"

 

[Richard Schollar]

Hi learningexcel123

You do realise what you posted wasn't an array formula though? I asume it was just an example. Be aware that you have a maximum formula length of 255 characters when using the FormulaArray property to assign an array formula.

 

[learningexcel123]

Thanks Hiker95, it worked