Using VBA To Enter A Sumproduct Formula Into A Cell

Joined
Mar 5, 2015
Messages
1
Reaction score
0
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:
=IF(SUMPRODUCT(--(Data!B2>=IF(Dashboard!$C$4="",1/1/2000,Dashboard!$C$4)),--(Data!B2<=IF(Dashboard!$E$4="",Calc!$B$6,Dashboard!$E$4)),IF(Dashboard!$H$4="",--(Data!D2<>""),--(Data!D2=Dashboard!$H$4)),IF(Dashboard!$J$4="",--(Data!E2<>""),--(Data!E2=Dashboard!$J$4)),IF(Dashboard!$M$4="",--(Data!F2<>""),--(Data!F2=Dashboard!$M$4)),--(Data!C2=""),--(Data!AK2<Calc!$B$6-60)),TRUE,FALSE)

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]<>"""")" & _
"!R[-2]C[5]=Dashboard!R4C13)),--(Data!R[-2]C[2]=""""),--(Data!R[-2]C[36]<Calc!R6C2-60)),TRUE,FALSE)"

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

Top