VBA: Unable to set the FormulaArray property of the range class error help

Trying to get my vba code to enter the following formula through a loop but getting an error. What am I doing wrong?

For row = 2 To lastrow
    
    Cells(row, 13).FormulaArray = "=SUMPRODUCT((INDIRECT(""""&tab&""!$A$5:$A$64"")=E2)*(INDIRECT(""""&tab&""!$C$5:$C$64"")=H2)*(INDIRECT(""""&tab&""!$B$5:$B$64"")=I2)*(INDIRECT(""""&tab&""!$D$5:$D$64"")=J2)*(INDIRECT(""""&tab&""!$E$4:$AR$4"")=F2)*IF(INDIRECT(""""&tab&""!$E$5:$AR$64"")="""",0,INDIRECT(""""&tab&""!$E$5:$AR$64"")))"
    
    row = row + 1
    
Next

Open in new window

Dmitriy KritskiyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamMicrosoft Excel ExpertCommented:
Can u upload sample file?
Dmitriy KritskiyAuthor Commented:
Here is a scaled down example:
Book1.xlsm
Saurabh Singh TeotiaCommented:
Dmitriy,

If you want to apply this formula through vba then you don't need to use indirect in their..as you can simply do this...

Sub Populate()

Dim lastrow As Long
Dim row As Long
Dim ws As Worksheet
Dim str As String

Set ws = ThisWorkbook.Sheets("main")
str = ThisWorkbook.Sheets("setup").Range("B5").Value

lastrow = ws.Cells(Cells.Rows.Count, "E").End(xlUp).row

ws.Range("M2:M" & lastrow).FormulaArray = "=SUMPRODUCT(((" & str & "!$A$5:$A$36)=E2)*((" & str & "!$C$5:$C$36)=H2)*((" & str & "!$B$5:$B$36)=I2)*((" & str & "!$D$5:$D$36)=J2)*((" & str & "!$E$4:$I$4)=F2)*IF((" & str & "!$E$5:$I$36)="""",0,(" & str & "!$E$5:$I$36)))"

End Sub

Open in new window


Saurabh...
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Dmitriy KritskiyAuthor Commented:
Saurabh, this doesn't work since it inserts the formula that only references row 2 parameters, so same data is pulled for all other rows. Also, I want to keep the loop approach since I also wanted to insert an if statement within the loop that would qualify which rows this formula would apply to based on values in some other columns, instead of applying the formula to all cells in column M at once.
Book1-1.xlsm
[ fanpages ]IT Services ConsultantCommented:
If you change the named range [tab] to a single character, say just [T], this line is executed successfully:

ws.Range(Cells(2&, "M"), Cells(lastrow, "M")).FormulaArray = "=SUMPRODUCT((INDIRECT(T&""!$A$5:$A$36"")=E2)*(INDIRECT(T&""!$C$5:$C$36"")=H2)*(INDIRECT(T&""!$B$5:$B$36"")=I2)*(INDIRECT(T&""!$D$5:$D$36"")=J2)*(INDIRECT(T&""!$E$4:$I$4"")=F2)*IF(INDIRECT(T&""!$E$5:$I$36"")="""",0,INDIRECT(T&""!$E$5:$I$36"")))"

Open in new window


That is, in-line...

Sub Populate()

Dim lastrow As Long
Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("main")

lastrow = ws.Range("E1048576").End(xlUp).row

ws.Range(Cells(2&, "M"), Cells(lastrow, "M")).FormulaArray = "=SUMPRODUCT((INDIRECT(T&""!$A$5:$A$36"")=E2)*(INDIRECT(T&""!$C$5:$C$36"")=H2)*(INDIRECT(T&""!$B$5:$B$36"")=I2)*(INDIRECT(T&""!$D$5:$D$36"")=J2)*(INDIRECT(T&""!$E$4:$I$4"")=F2)*IF(INDIRECT(T&""!$E$5:$I$36"")="""",0,INDIRECT(T&""!$E$5:$I$36"")))"

End Sub

Open in new window


The rest of the code (I have removed) is not required.


I suspect that the internal length of a FormulaArray value (255 characters) has been reached although the actual length of the string when queried is only 241 characters.
Q-28672291.xlsm
Saurabh Singh TeotiaCommented:
You can do this in that case...

Sub Populate()

    Dim lastrow As Long
    Dim rw As Long
    Dim ws As Worksheet
    Dim str As String

    Set ws = ThisWorkbook.Sheets("main")
    str = ThisWorkbook.Sheets("setup").Range("B5").Value

    lastrow = ws.Cells(Cells.Rows.Count, "E").End(xlUp).row

    For rw = 2 To lastrow

        ws.Range("M" & rw).FormulaArray = "=SUMPRODUCT(((" & str & "!$A$5:$A$36)=E" & rw & ")*((" & str & "!$C$5:$C$36)=H" & rw & ")*((" & str & "!$B$5:$B$36)=I" & rw & ")*((" & str & "!$D$5:$D$36)=J" & rw & ")*((" & str & "!$E$4:$I$4)=F" & rw & ")*IF((" & str & "!$E$5:$I$36)="""",0,(" & str & "!$E$5:$I$36)))"

    Next rw
End Sub

Open in new window


Also in your formula your rows was static i changed them as well now when i created loop..In additional when you do declaration avoid using a keyword for instance row is a keyword so i changed that rw and you don't have to add 1 to it when you create a loop since it will set a increment to two rather then 1

Saurabh...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saurabh Singh TeotiaCommented:
Dimtiry,

I would still recommend you applying the formula in one go rather then loop as it will be time consuming but leaving the choice to you what you want to do..and enclosed is the revised code where i fixed the earlier bug where it was all referring row-2 only...

Sub Populate1()

    Dim lastrow As Long
    Dim row As Long
    Dim ws As Worksheet
    Dim str As String

    Set ws = ThisWorkbook.Sheets("main")
    str = ThisWorkbook.Sheets("setup").Range("B5").Value

    lastrow = ws.Cells(Cells.Rows.Count, "E").End(xlUp).row

    ws.Range("M2:M" & lastrow).Formula = "=SUMPRODUCT(((" & str & "!$A$5:$A$36)=E2)*((" & str & "!$C$5:$C$36)=H2)*((" & str & "!$B$5:$B$36)=I2)*((" & str & "!$D$5:$D$36)=J2)*((" & str & "!$E$4:$I$4)=F2)*IF((" & str & "!$E$5:$I$36)="""",0,(" & str & "!$E$5:$I$36)))"

    ws.Range("M2:M" & lastrow).FormulaArray = ws.Range("M2:M" & lastrow).Formula

End Sub

Open in new window


Saurabh...
Dmitriy KritskiyAuthor Commented:
This is exactly what I need for my purposes! Thank you!!!
Saurabh Singh TeotiaCommented:
You are welcome..Happy to Help.. :-)
[ fanpages ]IT Services ConsultantCommented:
"This is exactly what I need for my purposes! Thank you!!!"

You're welcome.

Assuming you even looked at my suggestion.
Saurabh Singh TeotiaCommented:
Fanpages,

Solution which you provided has a bug similar to my earlier solution..If you refer to row number-->M25 formula in your worksheet..It's still referring to row-2 rather then row-25..Somehow its kind of way in vba array operates what i have found basis of my earlier experience that apply it as a normal formula and then change it on array then it will work properly...

In additional i believe he is looking for a loop solution rather then in 1 go since he wants to do some additional steps..

Hope this helps buddy in providing a bit of clarity what you are looking for..

Saurabh...
[ fanpages ]IT Services ConsultantCommented:
Thanks Saurabh.

I just fixed the setting of the FormulaArray, as the question requested, & advised why it most likely failed originally.

Additional issues with the code should have been discussed thereafter.

Just recognition that I contributed would have been courteous.  You kindly provided that, but it was not your place to do so.

Thanks again, all the same.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.