AboutPricingCommunityTeamsStart Free TrialLog in
Avatar of Shanan212
Shanan212
Flag for Canada asked on

Big formula error when using VBA

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))),"""")))"

Thanks!
Microsoft ExcelMicrosoft Office
Avatar of undefined
Last Comment
Shanan212
8/22/2022 - Mon
SOLUTION
Michael Fowler
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Shanan212
ASKER
Thank you. That is indeed the last option but I do want to squeeze it in if possible through any other methods?
SOLUTION
ChloesDad
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Michael Fowler
You could write some functions of your own that will perform some the calculations and then call these functions. This would achieve the same thing except the calculations would only be performed as required. Using Application.WorksheetFunction you can still call worksheet functions so that you do not need to reinvent the wheel
Michael Fowler
Or as suggested above you could develop a function that performs the entire calcaluation and then just call this function.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Rory Archibald
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
ChloesDad
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Shanan212
ASKER
Thanks all!

So the 'IfError' was masking my actual errors!
Rob Henson
I think Rorya's point is that the function within the formula doesn't look right.

Syntax for SUMIFS = SUMIFS(SumRange, CriteriaRange1, Criteria1, etc)

The formula above only has a single cell for the SumRange.

Thanks
Rob H
Rory Archibald
It was the sheet names that looked wrong. (remember it's R1C1 referencing so C2 = column B)
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Shanan212
ASKER
Sorry guys, I came back to work and the problem still exits. Partly because I pasted wrong 'VBA version of formula' in my question above

Here is the actual Excel Formula and below this is the VBA version. (Rob H, the sumifs is correct as its a '$C:$C' which would translate into C3)

Excel

=IF(AND(LEN(O2)>2,IFERROR(MAX(VLOOKUP($A2,Packing_Slip,11,FALSE),VLOOKUP($A2,Packing_Slip,15,FALSE),VLOOKUP($A2,Packing_Slip,19,FALSE)),0)>0),IF(AND(COUNTIFS('PO & SKU'!$D:$D,"=MULTI*")>0,ISNA(VLOOKUP($A2,Packing_Slip,8,FALSE))=FALSE),1,""),IF(AND($J2>0,O2<>"MULTISKU"),ROUNDUP((SUMIFS('PO & SKU'!$V:$V,'PO & SKU'!$B:$B,$A2,'PO & SKU'!$U:$U,"=S")+SUMIFS('PO & SKU'!$V:$V,'PO & SKU'!$B:$B,$A2,'PO & SKU'!$U:$U,"=D")+SUMIFS('PO & SKU'!$V:$V,'PO & SKU'!$B:$B,$A2,'PO & SKU'!$U:$U,"=P")-SUMIFS('PO & SKU'!$V:$V,'PO & SKU'!$B:$B,$A2,'PO & SKU'!$U:$U,"=O")+$J2)/(SUMIF('PO & SKU'!$B:$B,$A2,'PO & SKU'!$G:$G)),2),IF(O2="MULTISKU",IF(COUNTIF('PO & SKU'!$B:$B,'CONTAINER DATA'!$A4412)=COUNTIFS('PO & SKU'!$K:$K,"<>",'PO & SKU'!$B:$B,'CONTAINER DATA'!$A4412),1,(COUNTIFS('PO & SKU'!$K:$K,"<>",'PO & SKU'!$B:$B,'CONTAINER DATA'!$A4412)/COUNTIF('PO & SKU'!$B:$B,'CONTAINER DATA'!$A4412))),"")))

VBA

  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))),"""")))"

Error:

"Application Defined or Object Defined error"

Thanks again!
Rory Archibald
It's the same problem I pointed out originally. Look at the text each side of the first line continuation character:
SUMIFS('PO & SKU" & _
        "O & SKU'!C2

which will become:
SUMIFS('PO & SKUO & SKU'!C2
in your formula. You've duplicated part of the sheet name.
Shanan212
ASKER
Thanks! I went through and got it to this level. (still error) I have checked this/compared this with actual formula few times and still going through. Let me know if I am doing anything wrong.

     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'!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[2]C1)=" & _
        "COUNTIFS('PO & SKU'!C11,""<>"",'PO & SKU'!C2,'CONTAINER DATA'!R[2]C1),1,COUNTIFS('PO & SKU'!C11,""<>"",'PO & SKU'!C2,'CONTAINER DATA'!R[2]C1)/" & _
        "COUNTIF('PO & SKU'!C2,'CONTAINER DATA'!R[2]C1))),"""")))"
Your help has saved me hundreds of hours of internet surfing.
fblack61
Rory Archibald
I'm not 100% sure as your original formula appears to have one too many parentheses to me, but perhaps:

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'!C22," & _
                         "'PO & 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[2]C1)=COUNTIFS('PO & SKU'!C11,""<>"",'PO & SKU'!C2," & _
                         "'CONTAINER DATA'!R[2]C1),1,COUNTIFS('PO & SKU'!C11,""<>"",'PO & SKU'!C2,'CONTAINER DATA'!R[2]C1)/" & _
                         "COUNTIF('PO & SKU'!C2,'CONTAINER DATA'!R[2]C1)),"""")))"

Open in new window

Rory Archibald
Or perhaps, a little shorter:
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((SUM(SUMIFS('PO & SKU'!C22,'PO & SKU'!C2,RC1,'PO & SKU'!C21,{""S"",""D"",""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[2]C1)=COUNTIFS('PO & SKU'!C11,""<>"",'PO & SKU'!C2," & _
                        "'CONTAINER DATA'!R[2]C1),1,COUNTIFS('PO & SKU'!C11,""<>"",'PO & SKU'!C2,'CONTAINER DATA'!R[2]C1)/COUNTIF('PO & SKU'!C2," & _
                        "'CONTAINER DATA'!R[2]C1)),"""")))"

Open in new window

Shanan212
ASKER
Wow it works! Spent countless hours on my end. Do you know what was the problem?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rory Archibald
You had a misplaced parenthesis and one of your SUMIFS formulas was missing the data range for the first argument.
Shanan212
ASKER
gah! The formula still worked perfectly. I guess the way VBA breaks up the larger formulas plays a role.

I ll make sure I don't go this big next time.

Thanks and have a nice weekend!
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent