Using VBA To Enter A Sumproduct Formula Into A Cell

Mar 5, 2015
Reaction score
I am currently working on a number of macros, and I want to use VBA to input a formula into a cell, before running an advanced filter on this.

The formula that I am using is:

which I have tested, and works fine. When I input this into VBA it converts it into R1C1:

"=IF(SUMPRODUCT(--(Data!R[-2]C[1]>=IF(Dashboard!R4C3="""",1/1/2000,Dashboard!R4C3)),--(Data!R[-2]C[1]<=IF(Dashboard!R4C5="""",Calc!R6C2,Dashboard!R4C5)),IF(Dashboard!R4C8="""",--(Data!R[-2]C[3]<>""""),--(Data!R[-2]C[3]=Dashboard!R4C8)),IF(Dashboard!R4C10="""",--(Data!R[-2]C[4]<>""""),--(Data!R[-2]C[4]=Dashboard!R4C10)),IF(Dashboard!R4C13="""",--(Data!R[-2]C[5]<>"""")" & _

However, I keep on getting a message saying "Runtime 1004; application- defined of object- defined error"

If I run a manual advanced filter on the original formula; then this works correctly. I cannot understand why I keep on getting this error.

Any help would be appriciated.

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