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. — user4039065
– user4039065, Commented Sep 21, 2016 at 3:18
As stated in Range.FormulaArray Property (Excel) : "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
– Axel Richter, Commented Sep 21, 2016 at 5:34

Community · Accepted Answer · 2017-05-23 12:08:23Z

1

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.}

edited May 23, 2017 at 12:08

CommunityBot

11 silver badge

answered Sep 21, 2016 at 6:22

user4039065

Sign up to request clarification or add additional context in comments.

2 Comments

user4039065 Over a year ago

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.

WOLF81 Over a year ago

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

Nikolaos Polygenis · Accepted Answer · 2017-09-20 07:41:15Z

0

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}"

edited Sep 20, 2017 at 7:41

answered Sep 19, 2017 at 12:18

Nikolaos Polygenis

7018 silver badges13 bronze badges

Collectives™ on Stack Overflow

Excel VBA .FormulaArray Error

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related