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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.