troubleshooting Question

Big formula error when using VBA

Avatar of Shanan212
Shanan212Flag for Canada asked on
Microsoft OfficeMicrosoft Excel
18 Comments4 Solutions464 ViewsLast Modified:
Hi, I have the following formula that I am trying to enter using VBA and each time, I am getting 'Application Defined' error.

I am thinking, it has to do with the size of formula (no !@#$ lol) Is there any go around for this? I tried assigning whole formula to a string, then to the activecell formula, and still same error! The formula is perfectly working formula.

Any help is appreciated!

ActiveCell.FormulaR1C1 = _
        "=IF(AND(LEN(RC[4])>2,IFERROR(MAX(VLOOKUP(RC1,Packing_Slip,11,FALSE),VLOOKUP(RC1,Packing_Slip,15,FALSE),VLOOKUP(RC1,Packing_Slip,19,FALSE)),0)>0),IF(AND(COUNTIFS('PO & SKU'!C4,""=MULTI*"")>0,ISNA(VLOOKUP(RC1,Packing_Slip,8,FALSE))=FALSE),1,""""),IF(AND(RC10>0,RC[4]<>""MULTISKU""),ROUNDUP((SUMIFS('PO & SKU'!C22,'PO & SKU'!C2,RC1,'PO & SKU'!C21,""=S"")+SUMIFS('PO & SKU" & _
        "O & SKU'!C2,RC1,'PO & SKU'!C21,""=D"")+SUMIFS('PO & SKU'!C22,'PO & SKU'!C2,RC1,'PO & SKU'!C21,""=P"")-SUMIFS('PO & SKU'!C22,'PO & SKU'!C2,RC1,'PO & SKU'!C21,""=O"")+RC10)/(SUMIF('PO & SKU'!C2,RC1,'PO & SKU'!C7)),2),IF(RC[4]=""MULTISKU"",IF(COUNTIF('PO & SKU'!C2,'CONTAINER DATA'!R[4410]C1)=COUNTIFS('PO & SKU'!C11,""<>"",'PO & SKU'!C2,'CONTAINER DATA'!R[4410]C1),1,(CO" & _
        "O & SKU'!C11,""<>"",'PO & SKU'!C2,'CONTAINER DATA'!R[4410]C1)/COUNTIF('PO & SKU'!C2,'CONTAINER DATA'!R[4410]C1))),"""")))"

Join our community to see this answer!
Unlock 4 Answers and 18 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 18 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros