Excel VBA .FormulaArray Error

Question

The error is a 1004 error but I don't understand why. The formula in the cell would be:

{=INDEX(KPI!A:AQ,MATCH(1,(KPI!A:A=Monday!$K$1)*(KPI!C:C=Monday!B4),0),37)+INDEX(KPI!A:AQ,MATCH(1,(KPI!A:A=Monday!$K$1)*(KPI!C:C=Monday!B4),0),38)-INDEX(KPI!A:AQ,MATCH(1,(KPI!A:A=Monday!$K$1)*(KPI!C:C=Monday!B4),0),39)}

To get this formula into a macro I simply used the recording tool but it did not work.

Selection.FormulaArray = _
    "=INDEX(KPI!C[-3]:C[39],MATCH(1,(KPI!C[-3]=Monday!R1C11)*(KPI!C[-1]=Monday!RC[-2]),0),37)+INDEX(KPI!C[-3]:C[39],MATCH(1,(KPI!C[-3]=Monday!R1C11)*(KPI!C[-1]=Monday!RC[-2]),0),38)-INDEX(KPI!C[-3]:C[39],MATCH(1,(KPI!C[-3]=Monday!R1C11)*(KPI!C[-1]=Monday!RC[-2]),0),39)"

There is almost certainly a easier formula to accomplish what you are trying. Please add sample data together with expected results. — , Sep 21 '16 at 03:18
As stated in [Range.FormulaArray Property (Excel)](https://msdn.microsoft.com/en-us/library/office/ff837104.aspx) : "The FormulaArray property also has a character limit of 255." Your formula contains 264 characters. That's to much. So do what @Jeeped suggested. — Axel Richter, Sep 21 '16 at 05:34

score 1 · Answer 1 · edited May 23 '17 at 12:08

As long as you insist on doing things the hard way,

Option Explicit

Sub wqer()
    With ThisWorkbook
        .Worksheets("KPI").Name = "K"
        .Worksheets("Monday").Name = "M"
        'geez - decide what cell you want without Selection¹ !!??!!
        Selection.FormulaArray = _
            "=INDEX(K!C[-3]:C[39],MATCH(1,(K!C[-3]=M!R1C11)*(K!C[-1]=M!RC[-2]),0),37)+INDEX(K!C[-3]:C[39],MATCH(1,(K!C[-3]=M!R1C11)*(K!C[-1]=M!RC[-2]),0),38)-INDEX(K!C[-3]:C[39],MATCH(1,(K!C[-3]=M!R1C11)*(K!C[-1]=M!RC[-2]),0),39)"
        .Worksheets("K").Name = "KPI"
        .Worksheets("M").Name = "Monday"
    End With
End Sub

Renaming your worksheets back and forth puts your formula under the built-in character limit² noted by Axel Richter for the time it takes to insert the array formula into the cell. This will put an undue calculation demand on your workbook but it may be an acceptable solution for you. Consider changing the application's calculation mode to manual for the duration of the operation.

.¹ _{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}

² _{See Range.FormulaArray property.}

btw, My tests show that you are using many wide and full column references. That's probably not an efficient means of calculating an array formula. — , Sep 21 '16 at 06:31
I'll give this a shot tomorrow. I'm very new at VBA and without lessons,so I'm not the least surprised that my formulas/coding is over complicated. On that I will be learning a lot in the near future. I've just been making do of what works for me at the time. I do appreciate the feedback. This worked. I just have a lot to learn in Excel as well as VBA but I'm at least getting somewhere. The only VBA I know is from learning the code from recording in excel and examples from this site. My spreadsheet has a lot of work, but it's functional. Thanks for the help — WOLF81, Sep 21 '16 at 21:41

Nikolaos Polygenis · Answer 2 · 2017-09-20T07:41:15.940

You can also try this(
please don't run it from the VBE, try to run it from sheets environment. Go to Developer-Macros-Your Macro -Run or Run it from a button or shortcut and it will work without problem):

Selection.Formula = _
    "=INDEX(KPI!C[-3]:C[39],MATCH(1,(KPI!C[-3]=Monday!R1C11)*(KPI!C[-1]=Monday!RC[-2]),0),37)+INDEX(KPI!C[-3]:C[39],MATCH(1,(KPI!C[-3]=Monday!R1C11)*(KPI!C[-1]=Monday!RC[-2]),0),38)-INDEX(KPI!C[-3]:C[39],MATCH(1,(KPI!C[-3]=Monday!R1C11)*(KPI!C[-1]=Monday!RC[-2]),0),39)"

SendKeys "{F2}"
SendKeys "^+{ENTER}"

Excel VBA .FormulaArray Error

2 Answers2