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