Big formula problem/error in excel VBA

    ActiveCell.FormulaR1C1 = _
        "=IF(IFERROR(VLOOKUP(RC1,Container_List,1,FALSE)=RC1,FALSE),IF(OR(IFERROR(VLOOKUP(RC2,PO1_List,1,FALSE)=RC2,FALSE),IFERROR(VLOOKUP(RC2,PO2_List,1,FALSE)=RC2,FALSE),IFERROR(VLOOKUP(RC2,PO3_List,1,FALSE)=RC2,FALSE),IFERROR(VLOOKUP(RC3,PO1_List,1,FALSE)=RC3,FALSE),IFERROR(VLOOKUP(RC3,PO2_List,1,FALSE)=RC3,FALSE),IFERROR(VLOOKUP(RC4,PO3_List,1,FALSE)=RC4,FALSE),IFERROR(V" & _
        "C5,PO1_List,1,FALSE)=RC5,FALSE),IFERROR(VLOOKUP(RC5,PO2_List,1,FALSE)=RC5,FALSE),IFERROR(VLOOKUP(RC5,PO3_List,1,FALSE)=RC5,FALSE)),IF(AND(RC17<>"""",IFERROR(VLOOKUP(RC1,Container_List,17,FALSE),FALSE)<>""""),IF(RC17=IFERROR(VLOOKUP(RC1,Container_List,17,FALSE),FALSE),""ETA MATCH"", ""NEW ETA""),IF(AND(RC17<>"""",IFERROR(VLOOKUP(RC1,Container_List,17,FALSE),FALSE)=""" & _
        " ETA"",""NO ETA"")),""NEW CONT""),""NEW CONT"")"

Open in new window


Hi, I have the above formula. I have to put this formula via VBA into a cell and drag it down as the 'sheet' get cleared every time.

So I recorded a macro and it came out to this

    Range("S1").Select

    ActiveCell.FormulaR1C1 = _
        "=IF(IFERROR(VLOOKUP(RC1,Container_List,1,FALSE)=RC1,FALSE),IF(OR(IFERROR(VLOOKUP(RC2,PO1_List,1,FALSE)=RC2,FALSE),IFERROR(VLOOKUP(RC2,PO2_List,1,FALSE)=RC2,FALSE),IFERROR(VLOOKUP(RC2,PO3_List,1,FALSE)=RC2,FALSE),IFERROR(VLOOKUP(RC3,PO1_List,1,FALSE)=RC3,FALSE),IFERROR(VLOOKUP(RC3,PO2_List,1,FALSE)=RC3,FALSE),IFERROR(VLOOKUP(RC4,PO3_List,1,FALSE)=RC4,FALSE),IFERROR(V" & _
        "C5,PO1_List,1,FALSE)=RC5,FALSE),IFERROR(VLOOKUP(RC5,PO2_List,1,FALSE)=RC5,FALSE),IFERROR(VLOOKUP(RC5,PO3_List,1,FALSE)=RC5,FALSE)),IF(AND(RC17<>"""",IFERROR(VLOOKUP(RC1,Container_List,17,FALSE),FALSE)<>""""),IF(RC17=IFERROR(VLOOKUP(RC1,Container_List,17,FALSE),FALSE),""ETA MATCH"", ""NEW ETA""),IF(AND(RC17<>"""",IFERROR(VLOOKUP(RC1,Container_List,17,FALSE),FALSE)=""" & _
        " ETA"",""NO ETA"")),""NEW CONT""),""NEW CONT"")"""
  
  Range("S1").Select

    Selection.AutoFill Destination:=Range("S1:S55")

Open in new window


Now it is giving an error on the line which starts with

ActiveCell.FormulaR1C1 = ...

The error is: Application defined or object defined error (1004)

The formula runs fine in excel. I suspect one of the reson might be where the "_" or breaks are put in (they might be breaking the formula far away) but I am inexperienced with this.

Any help is appreciated!
Temp.PNG
LVL 13
Shanan212Asked:
Who is Participating?
 
nutschCommented:
Thanks.

This worked for me:

[S1].FormulaR1C1 = "=IF(IFERROR(VLOOKUP(RC1,Container_List,1,FALSE)=RC1,FALSE)," & _
                    "IF(OR(IFERROR(VLOOKUP(RC2,PO1_List,1,FALSE)=RC2,FALSE)," & _
                    "IFERROR(VLOOKUP(RC2,PO2_List,1,FALSE)=RC2,FALSE)," & _
                    "IFERROR(VLOOKUP(RC2,PO3_List,1,FALSE)=RC2,FALSE)," & _
                    "IFERROR(VLOOKUP(RC3,PO1_List,1,FALSE)=RC3,FALSE)," & _
                    "IFERROR(VLOOKUP(RC3,PO2_List,1,FALSE)=RC3,FALSE)," & _
                    "IFERROR(VLOOKUP(RC4,PO3_List,1,FALSE)=RC4,FALSE)," & _
                    "IFERROR(VLOOKUP(RC5,PO1_List,1,FALSE)=RC5,FALSE)," & _
                    "IFERROR(VLOOKUP(RC5,PO2_List,1,FALSE)=RC5,FALSE)," & _
                    "IFERROR(VLOOKUP(RC5,PO3_List,1,FALSE)=RC5,FALSE))," & _
                    "IF(AND(RC17<>"""",IFERROR(VLOOKUP(RC1,Container_List,17,FALSE),FALSE)<>"""")," & _
                    "IF(RC17=IFERROR(VLOOKUP(RC1,Container_List,17,FALSE),FALSE),""ETA MATCH"", ""NEW ETA"")," & _
                    "IF(AND(RC17<>"""",IFERROR(VLOOKUP(RC1,Container_List,17,FALSE),FALSE)=""""),""NEW ETA"",""NO ETA""))," & _
                    """NEW CONT""),""NEW CONT"")"

Open in new window

0
 
nutschCommented:
You're missing stuff at both ends.
Try this.

ActiveCell.FormulaR1C1 = _
        "=IF(IFERROR(VLOOKUP(RC1,Container_List,1,FALSE)=RC1,FALSE),IF(OR(IFERROR(VLOOKUP(RC2,PO1_List,1,FALSE)=RC2,FALSE),IFERROR(VLOOKUP(RC2,PO2_List,1,FALSE)=RC2,FALSE),IFERROR(VLOOKUP(RC2,PO3_List,1,FALSE)=RC2,FALSE),IFERROR(VLOOKUP(RC3,PO1_List,1,FALSE)=RC3,FALSE),IFERROR(VLOOKUP(RC3,PO2_List,1,FALSE)=RC3,FALSE),IFERROR(VLOOKUP(RC4,PO3_List,1,FALSE)=RC4,FALSE),IFERROR(VLOOKUP(" & _
        "C5,PO1_List,1,FALSE)=RC5,FALSE),IFERROR(VLOOKUP(RC5,PO2_List,1,FALSE)=RC5,FALSE),IFERROR(VLOOKUP(RC5,PO3_List,1,FALSE)=RC5,FALSE)),IF(AND(RC17<>"""",IFERROR(VLOOKUP(RC1,Container_List,17,FALSE),FALSE)<>""""),IF(RC17=IFERROR(VLOOKUP(RC1,Container_List,17,FALSE),FALSE),""ETA MATCH"", ""NEW ETA""),IF(AND(RC17<>"""",IFERROR(VLOOKUP(RC1,Container_List,17,FALSE),FALSE)=""""," & _
        " ETA"",""NO ETA"")),""NEW CONT""),""NEW CONT"")"""

Open in new window

0
 
Glenn RayExcel VBA DeveloperCommented:
The two line breaks are definitely the culprits.

I think I've resolved the first one, but the second one (between lines 3 & 4) isn't clear to me.  Is there anyway you can copy the actual original formula and paste that here?

The following is a formatted version of the formula I have so far.



ActiveCell.FormulaR1C1 = _
        "=IF(IFERROR(VLOOKUP(RC1,Container_List,1,FALSE)=RC1,FALSE)," & _
        "IF(OR(IFERROR(VLOOKUP(RC2,PO1_List,1,FALSE)=RC2,FALSE)," & _
        "IFERROR(VLOOKUP(RC2,PO2_List,1,FALSE)=RC2,FALSE)," & _
        "IFERROR(VLOOKUP(RC2,PO3_List,1,FALSE)=RC2,FALSE)," & _
        "IFERROR(VLOOKUP(RC3,PO1_List,1,FALSE)=RC3,FALSE)," & _
        "IFERROR(VLOOKUP(RC3,PO2_List,1,FALSE)=RC3,FALSE)," & _
        "IFERROR(VLOOKUP(RC4,PO3_List,1,FALSE)=RC4,FALSE)," & _
        "IFERROR(VLOOKUP(RC5,PO1_List,1,FALSE)=RC5,FALSE)," & _
        "IFERROR(VLOOKUP(RC5,PO2_List,1,FALSE)=RC5,FALSE)," & _
        "IFERROR(VLOOKUP(RC5,PO3_List,1,FALSE)=RC5,FALSE))," & _
        "IF(AND(RC17<>"""",IFERROR(VLOOKUP(RC1,Container_List,17,FALSE),FALSE)<>"""")," & _
        "IF(RC17=IFERROR(VLOOKUP(RC1,Container_List,17,FALSE),FALSE),""ETA MATCH"", ""NEW ETA"")," & _
        "IF(AND(RC17<>"""",IFERROR(VLOOKUP(RC1,Container_List,17,FALSE),FALSE)= """")," & _
        " ETA"",""NO ETA"")),""NEW CONT""),""NEW CONT"")"""

Open in new window

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
nutschCommented:
You can also simplify all your
IFERROR(VLOOKUP(RC2,PO3_List,1,FALSE)=RC2,FALSE)
to
NOT(ISNA(MATCH(RC2,PO3_List,0)))

and your whole
 block

OR(IFERROR(VLOOKUP(RC2,PO1_List,1,FALSE)=RC2,FALSE)," & _
        "IFERROR(VLOOKUP(RC2,PO2_List,1,FALSE)=RC2,FALSE)," & _
        "IFERROR(VLOOKUP(RC2,PO3_List,1,FALSE)=RC2,FALSE)," & _
        "IFERROR(VLOOKUP(RC3,PO1_List,1,FALSE)=RC3,FALSE)," & _
        "IFERROR(VLOOKUP(RC3,PO2_List,1,FALSE)=RC3,FALSE)," & _
        "IFERROR(VLOOKUP(RC4,PO3_List,1,FALSE)=RC4,FALSE)," & _
        "IFERROR(VLOOKUP(RC5,PO1_List,1,FALSE)=RC5,FALSE)," & _
        "IFERROR(VLOOKUP(RC5,PO2_List,1,FALSE)=RC5,FALSE)," & _
        "IFERROR(VLOOKUP(RC5,PO3_List,1,FALSE)=RC5,FALSE))," & _

Open in new window


To

NOT(AND(ISNA(MATCH(RC2,PO1_List,0)," & _
        "ISNA(MATCH(RC2,PO2_List,0)," & _
        "ISNA(MATCH(RC2,PO3_List,0)," & _
        "ISNA(MATCH(RC3,PO1_List,0)," & _
        "ISNA(MATCH(RC3,PO2_List,0)," & _
        "ISNA(MATCH(RC4,PO3_List,0)," & _
        "ISNA(MATCH(RC5,PO1_List,0)," & _
        "ISNA(MATCH(RC5,PO2_List,0)," & _
        "ISNA(MATCH(RC5,PO3_List,0))," & _

Open in new window


Thomas
0
 
nutschCommented:
I meant of course

NOT(AND(ISNA(MATCH(RC2,PO1_List,0))," & _
        "ISNA(MATCH(RC2,PO2_List,0))," & _
        "ISNA(MATCH(RC2,PO3_List,0))," & _
        "ISNA(MATCH(RC3,PO1_List,0))," & _
        "ISNA(MATCH(RC3,PO2_List,0))," & _
        "ISNA(MATCH(RC4,PO3_List,0))," & _
        "ISNA(MATCH(RC5,PO1_List,0))," & _
        "ISNA(MATCH(RC5,PO2_List,0))," & _
        "ISNA(MATCH(RC5,PO3_List,0)))," & _

Open in new window

0
 
Shanan212Author Commented:
    ActiveCell.FormulaR1C1 = _
    "=IF(IFERROR(VLOOKUP(RC1,Container_List,1,FALSE)=RC1,FALSE)," & _
"NOT(AND(ISNA(MATCH(RC2,PO1_List,0))," & _
"ISNA(MATCH(RC2,PO2_List,0))," & _
"ISNA(MATCH(RC2,PO3_List,0))," & _
"ISNA(MATCH(RC3,PO1_List,0))," & _
"ISNA(MATCH(RC3,PO2_List,0))," & _
"ISNA(MATCH(RC4,PO3_List,0))," & _
"ISNA(MATCH(RC5,PO1_List,0))," & _
"ISNA(MATCH(RC5,PO2_List,0))," & _
"ISNA(MATCH(RC5,PO3_List,0)))," & _
    "IF(AND(RC17<>"""",IFERROR(VLOOKUP(RC1,Container_List,17,FALSE),FALSE)<>"""")," & _
    "IF(RC17=IFERROR(VLOOKUP(RC1,Container_List,17,FALSE),FALSE),""ETA MATCH"", ""NEW ETA"")," & _
    "IF(AND(RC17<>"""",IFERROR(VLOOKUP(RC1,Container_List,17,FALSE),FALSE)= """")," & _
    " ETA"",""NO ETA"")),""NEW CONT""),""NEW CONT"")"""

Open in new window


That did not work :/ same error

The formula in excel is

=IF(IFERROR(VLOOKUP($A1,Container_List,1,FALSE)=$A1,FALSE),IF(OR(IFERROR(VLOOKUP($B1,PO1_List,1,FALSE)=$B1,FALSE),IFERROR(VLOOKUP($B1,PO2_List,1,FALSE)=$B1,FALSE),IFERROR(VLOOKUP($B1,PO3_List,1,FALSE)=$B1,FALSE),IFERROR(VLOOKUP($C1,PO1_List,1,FALSE)=$C1,FALSE),IFERROR(VLOOKUP($C1,PO2_List,1,FALSE)=$C1,FALSE),IFERROR(VLOOKUP($D1,PO3_List,1,FALSE)=$D1,FALSE),IFERROR(VLOOKUP($E1,PO1_List,1,FALSE)=$E1,FALSE),IFERROR(VLOOKUP($E1,PO2_List,1,FALSE)=$E1,FALSE),IFERROR(VLOOKUP($E1,PO3_List,1,FALSE)=$E1,FALSE)),IF(AND($Q1<>"",IFERROR(VLOOKUP($A1,Container_List,17,FALSE),FALSE)<>""),IF($Q1=IFERROR(VLOOKUP($A1,Container_List,17,FALSE),FALSE),"ETA MATCH", "NEW ETA"),IF(AND($Q1<>"",IFERROR(VLOOKUP($A1,Container_List,17,FALSE),FALSE)=""),"NEW ETA","NO ETA")),"NEW CONT"),"NEW CONT")

Open in new window

0
 
Julie ThomasCommented:
I think this might do it:
 
"=IF(IFERROR(VLOOKUP($A1,Container_List,1,FALSE)=$A1,FALSE),IF(OR(IFERROR(VLOOKUP($B1,PO1_List,1,FALSE)=$B1,FALSE)," & _
"IFERROR(VLOOKUP($B1,PO2_List,1,FALSE)=$B1,FALSE),IFERROR(VLOOKUP($B1,PO3_List,1,FALSE)=$B1,FALSE)," & _
"IFERROR(VLOOKUP($C1,PO1_List,1,FALSE)=$C1,FALSE),IFERROR(VLOOKUP($C1,PO2_List,1,FALSE)=$C1,FALSE)," & _
"IFERROR(VLOOKUP($D1,PO3_List,1,FALSE)=$D1,FALSE),IFERROR(VLOOKUP($E1,PO1_List,1,FALSE)=$E1,FALSE)," & _
"IFERROR(VLOOKUP($E1,PO2_List,1,FALSE)=$E1,FALSE),IFERROR(VLOOKUP($E1,PO3_List,1,FALSE)=$E1,FALSE))," & _
"IF(AND($Q1<>"",IFERROR(VLOOKUP($A1,Container_List,17,FALSE),FALSE)<>"")," & _
"IF($Q1=IFERROR(VLOOKUP($A1,Container_List,17,FALSE),FALSE),"ETA MATCH", "NEW ETA"),IF(AND($Q1<>""," & _
"IFERROR(VLOOKUP($A1,Container_List,17,FALSE),FALSE)=""),"NEW ETA","NO ETA")),"NEW CONT"),"NEW CONT")"

Open in new window

0
 
Shanan212Author Commented:
    ActiveCell.FormulaR1C1 = _
   "=IF(IFERROR(VLOOKUP($A1,Container_List,1,FALSE)=$A1,FALSE),IF(OR(IFERROR(VLOOKUP($B1,PO1_List,1,FALSE)=$B1,FALSE)," & _
   "IFERROR(VLOOKUP($B1,PO2_List,1,FALSE)=$B1,FALSE),IFERROR(VLOOKUP($B1,PO3_List,1,FALSE)=$B1,FALSE)," & _
   "IFERROR(VLOOKUP($C1,PO1_List,1,FALSE)=$C1,FALSE),IFERROR(VLOOKUP($C1,PO2_List,1,FALSE)=$C1,FALSE)," & _
   "IFERROR(VLOOKUP($D1,PO3_List,1,FALSE)=$D1,FALSE),IFERROR(VLOOKUP($E1,PO1_List,1,FALSE)=$E1,FALSE)," & _
   "IFERROR(VLOOKUP($E1,PO2_List,1,FALSE)=$E1,FALSE),IFERROR(VLOOKUP($E1,PO3_List,1,FALSE)=$E1,FALSE))," & _
   "IF(AND($Q1<>"""",IFERROR(VLOOKUP($A1,Container_List,17,FALSE),FALSE)<>"""")," & _
   "IF($Q1=IFERROR(VLOOKUP($A1,Container_List,17,FALSE),FALSE),""ETA MATCH"", ""NEW ETA""),IF(AND($Q1<>""""," & _
   "IFERROR(VLOOKUP($A1,Container_List,17,FALSE),FALSE)=""""),""NEW ETA"",""NO ETA"")),""NEW CONT""),""NEW CONT"")"

Open in new window


Didn't work either. I put double quotes around string values...no help :/
0
 
redmondbCommented:
Hi, Shanan212.

Out of curiosity, I recorded entering the cell formula in post 37390106. As in your question, the recorder made mincemeat of the formula, dropping bits of it.

I've no doubt that someone will correctly repair Excel's error and give you a valid line of code - however I strongly recommend that you instead split the formula out over a number of cells. When you have to make a change to the formula in a day/week/month you'll be hugely grateful to your earlier self!

Regards,
Brian.
0
 
Shanan212Author Commented:
Hi,

The user is not willing to break up formula. If anyone can make this work via multiple lines, it would be really appreciated!

Thanks!
0
 
Shanan212Author Commented:
I've requested that this question be deleted for the following reason:

No further help
0
 
nutschCommented:
Can you do a copy paste of the actual entry of the formula (copy from the formula bar in excel)?

Can you also also not request closing for not receiving an answer within 12 hours on a Sunday night? All experts are volunteers and some have jobs, families, beds, etc.

Thanks,

T
0
 
Shanan212Author Commented:
Appologies!

The formula as posted above is

=IF(IFERROR(VLOOKUP($A1,Container_List,1,FALSE)=$A1,FALSE),IF(OR(IFERROR(VLOOKUP($B1,PO1_List,1,FALSE)=$B1,FALSE),IFERROR(VLOOKUP($B1,PO2_List,1,FALSE)=$B1,FALSE),IFERROR(VLOOKUP($B1,PO3_List,1,FALSE)=$B1,FALSE),IFERROR(VLOOKUP($C1,PO1_List,1,FALSE)=$C1,FALSE),IFERROR(VLOOKUP($C1,PO2_List,1,FALSE)=$C1,FALSE),IFERROR(VLOOKUP($D1,PO3_List,1,FALSE)=$D1,FALSE),IFERROR(VLOOKUP($E1,PO1_List,1,FALSE)=$E1,FALSE),IFERROR(VLOOKUP($E1,PO2_List,1,FALSE)=$E1,FALSE),IFERROR(VLOOKUP($E1,PO3_List,1,FALSE)=$E1,FALSE)),IF(AND($Q1<>"",IFERROR(VLOOKUP($A1,Container_List,17,FALSE),FALSE)<>""),IF($Q1=IFERROR(VLOOKUP($A1,Container_List,17,FALSE),FALSE),"ETA MATCH", "NEW ETA"),IF(AND($Q1<>"",IFERROR(VLOOKUP($A1,Container_List,17,FALSE),FALSE)=""),"NEW ETA","NO ETA")),"NEW CONT"),"NEW CONT")

Open in new window

0
 
Shanan212Author Commented:
Worked for me as well!

Going to see what went wrong!

Thanks!
0
 
Glenn RayExcel VBA DeveloperCommented:
Glad that worked.  Until we saw the original formula, it was very difficult to see what missing characters there were in the second break.

I suspect that this happened because there are more than 256 characters in the original formula (777, including spaces, in fact).  This would have accounted for two breaks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.