Solved

Big formula error when using VBA

Posted on 2013-11-26
18
388 Views
Last Modified: 2013-11-29
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!
0
Comment
Question by:Shanan212
  • 6
  • 6
  • 3
  • +2
18 Comments
 
LVL 23

Assisted Solution

by:Michael74
Michael74 earned 30 total points
ID: 39679254
Use some calculation cells. That is perform some of formula work in other cells and call the results from these cells in the main formula. As an added benefit it will make your formula easier to read. If the workbook will be used by others place the calculation cells on a hidden sheet.


Michael
0
 
LVL 13

Author Comment

by:Shanan212
ID: 39679262
Thank you. That is indeed the last option but I do want to squeeze it in if possible through any other methods?
0
 
LVL 15

Assisted Solution

by:ChloesDad
ChloesDad earned 70 total points
ID: 39679308
If you are writing the formula in vba, then why not use vba to do all of the logic rather than do it all in the cell

then you will have

If ........... then
  activecell.formula = xx
ELSE
  IF ........ then
       activecell.formula = xx
  ELSE
        activecell.formula= xx
  ENDIf
ENDIF

etc
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 23

Expert Comment

by:Michael74
ID: 39679311
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
0
 
LVL 23

Expert Comment

by:Michael74
ID: 39679316
Or as suggested above you could develop a function that performs the entire calcaluation and then just call this function.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 400 total points
ID: 39680125
Your actual formula string looks wrong to me. What is it supposed to be when in a cell?

Currently you are trying to input this (I've highlighted the bits that look odd):

=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 & SKUO & 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,(COO & SKU'!C11,"<>",'PO & SKU'!C2,'CONTAINER DATA'!R[4410]C1)/COUNTIF('PO & SKU'!C2,'CONTAINER DATA'!R[4410]C1))),"")))
0
 
LVL 15

Assisted Solution

by:ChloesDad
ChloesDad earned 70 total points
ID: 39680529
Those will be sheet names, but the last one that you highlight is missing a leading '


COO & SKU'!C11 becomes 'COO & SKU'!C11

You should check that all the sheet names are entered correctly in your formula
0
 
LVL 13

Author Closing Comment

by:Shanan212
ID: 39680599
Thanks all!

So the 'IfError' was masking my actual errors!
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39680604
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
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39680768
It was the sheet names that looked wrong. (remember it's R1C1 referencing so C2 = column B)
0
 
LVL 13

Author Comment

by:Shanan212
ID: 39683777
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!
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39683843
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.
0
 
LVL 13

Author Comment

by:Shanan212
ID: 39683873
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))),"""")))"
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39684847
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

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39684852
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

0
 
LVL 13

Author Comment

by:Shanan212
ID: 39685314
Wow it works! Spent countless hours on my end. Do you know what was the problem?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39685358
You had a misplaced parenthesis and one of your SUMIFS formulas was missing the data range for the first argument.
0
 
LVL 13

Author Comment

by:Shanan212
ID: 39685539
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!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question