Syntax to insert an array formula in a cell

  • Thread starter Thread starter mikeburg
  • Start date Start date
M

mikeburg

What would be the proper syntax to have VBA to insert an array formul
in a cell? For example:

SheetData.Range("F4") = "=SUM(F7:F70)" inserts the formula.

However, the following does not work,
SheetData.Range("E4") = "{=SUM(--SUBSTITUTE(0 & E7:E70, " - ",""))}"

Any ideas. Thanks for all your help. mikebur
 
Hi Mike

You need to use the FormulaArray function

This code should work :

Range("E4").FormulaArray = "=SUM(--SUBSTITUTE(0 & R[3]C:R[66]C, "" -
"",""""))"

Thanks
Karthik Bhat
 
From Help:

Worksheets("Sheet1").Range("E1:E3").FormulaArray = _
"=Sum(R1C1:R3C3)"

Tim
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Evaluate an Array Formula before inserting it in a cell 9
Macro to change columns - monthly question 3
Ongoing Issue - I have tried to clarify so that you understand 7
Recording Macro - Insert Formula 2
Evalute Array Formula in VBA code 2
Relative Referencing Formulas in Arrays 1
Formula Array - Syntax Error 4
Copy paste data from a column to a row 0

Back
Top