learningexcel123
New Member
- Joined
- May 1, 2010
- Messages
- 13
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
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