Link to home
Start Free TrialLog in
Avatar of Shanan212
Shanan212Flag for Canada

asked on

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
Avatar of nutsch
nutsch
Flag of United States of America image
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

Avatar of Glenn Ray
Glenn Ray
Flag of United States of America image
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

Avatar of nutsch
nutsch
Flag of United States of America image
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
Avatar of nutsch
nutsch
Flag of United States of America image
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

Avatar of Shanan212
Shanan212
Flag of Canada image

ASKER

    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

Avatar of Julie Thomas
Julie Thomas
Flag of United States of America image
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

Avatar of Shanan212
Shanan212
Flag of Canada image

ASKER

    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 :/
Avatar of redmondb
redmondb
Flag of Afghanistan image
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.
Avatar of Shanan212
Shanan212
Flag of Canada image

ASKER

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!
Avatar of Shanan212
Shanan212
Flag of Canada image

ASKER

I've requested that this question be deleted for the following reason:

No further help
Avatar of nutsch
nutsch
Flag of United States of America image
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
Avatar of Shanan212
Shanan212
Flag of Canada image

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of nutsch
nutsch
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Shanan212
Shanan212
Flag of Canada image

ASKER

Worked for me as well!

Going to see what went wrong!

Thanks!
Avatar of Glenn Ray
Glenn Ray
Flag of United States of America image
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.