+ Reply to Thread
Results 1 to 11 of 11

Macro String too long

  1. 06-24-2013, 04:21 PM #1
    sam8114 is offline
    Registered User
    Join Date
    06-24-2013
    Location
    SC
    MS-Off Ver
    Excel 2010
    Posts
    26

    Macro String too long

    I am trying to write a code that creates a new column and pastes the following formula into cell AX2. However, my formula is too long and it keeps giving me a compile end of statement error. Is there some type of code that can connect the formula when it breaks up into two lines? Or maybe I have some other issue... I've never had a formula longer than one line so I've never had this problem...


    Range("AX1").Select
    ActiveCell.FormulaR1C1 = "Employee"
    Range("AX2").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(AND(RC[-4]=""FDC"",RC[-3]=""VBMS"",RC[-37]=""DEV""),VLOOKUP(RC[-47],TeamDigitAssignments!R2C3:R51C11,4,0),IF(AND(RC[-4]=""FDC"",RC[-3]=""VBMS"",RC[-37]=""EVD""),VLOOKUP(RC[-47],TeamDigitAssignments!R2C3:R51C11,4,0),IF(AND(RC[-4]=""FDC"",RC[-3]=""VBMS"",RC[-37]=""All Evidence Received, Not RFD""),VLOOKUP(RC[-47],TeamDigitAssignments!R2C3:R51C11,4,0),IF(AND(RC[-4] =""FDC"", RC[-37]=""DEV""),VLOOKUP(RC[-47],TeamDigitAssignments!R2C3:R51C11,4,0),IF(AND(RC[-4]=""FDC"",RC[-37]=""EVD""),VLOOKUP(RC[-47],TeamDigitAssignments!R2C3:R51C11,4,0),IF(AND(RC[-4]=""FDC"",RC[-37]=""All Evidence Received, Not RFD""),VLOOKUP(RC[-47],TeamDigitAssignments!R2C3:R51C11,4,0),IF(AND(RC[-3]=""VBMS"",RC[-37]=""DEV""),VLOOKUP(RC[-47],TeamDigitAssignments!R2C3:R51C11,3,0),IF(AND(RC[-3]=""VBMS"",RC[-37]=""EVD""),VLOOKUP(RC[-47],TeamDigitAssignments!R2C3:R51C11,3,0),IF(AND(RC[-3]=""VBMS"",RC[-37]=""All Evidence Received, Not RFD""),VLOOKUP(RC[-47],TeamDigitAssignments!R2C3:R51C11,3,0),IF(AND(RC[-37]=""DEV"",RC[-4]<>""FDC"",RC[-3]<>""V
    BMS""),VLOOKUP(RC[-47],TeamDigitAssignments!R2C3:R51C11,2,0),IF(AND(RC[-37]=""EVD"",RC[-4]<>""FDC"",RC[-3]<> "VBMS""),VLOOKUP(RC[-47],TeamDigitAssignments!R2C3:R51C11,2,0),IF(AND(RC[-37]=""All Evidence Received, Not RFD"",RC[-4]<>""FDC"",RC[-3]<>""VBMS""),VLOOKUP(RC[-47],TeamDigitAssignments!R2C3:R51C11,2,0),IF(RC[-37]=""DEC"",VLOOKUP(RC[-47],TeamDigitAssignments!R2C3:R51C11,5,0),IF(RC[-37]=""WRK"",VLOOKUP(RC[-47],TeamDigitAssignments!R2C3:R51C11,8,0),IF(RC[-37]=""AWD"", VLOOKUP(RC[-47],TeamDigitAssignments!R2C3:R51C11,8,0),IF(RC[-37]=""AUT"",VLOOKUP(RC[-47],TeamDigitAssignments!R2C3:R51C11,9,0)))))))))))))))))"

    It gives me an error right where the ""V
    BMS"" is located. Any suggestions?
    Thanks for you help!
    Reply With QuoteRegister To Reply
  2. 06-24-2013, 04:52 PM #2
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,454

    Re: Macro String too long

    Can you post the formula that is actually in the cell.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 06-25-2013, 09:24 AM #3
    sam8114 is offline
    Registered User
    Join Date
    06-24-2013
    Location
    SC
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Macro String too long

    =IF(AND(AT2="FDC",AU2="VBMS",M2="DEV"),VLOOKUP(C2,TeamDigitAssignments!$C$2:$K$51,4,0),IF(AND(AT2="FDC",AU2="VBMS",M2="EVD"),VLOOKUP(C2,TeamDigitAssignments!$C$2:$K$51,4,0),IF(AND(AT2="FDC",AU2="VBMS",M2="All Evidence Received, Not RFD"),VLOOKUP(C2,TeamDigitAssignments!$C$2:$K$51,4,0),IF(AND(AT2="FDC",M2="DEV"),VLOOKUP(C2,TeamDigitAssignments!$C$2:$K$51,4,0),IF(AND(AT2="FDC",M2="EVD"),VLOOKUP(C2,TeamDigitAssignments!$C$2:$K$51,4,0),IF(AND(AT2="FDC",M2="All Evidence Received, Not RFD"),VLOOKUP(C2,TeamDigitAssignments!$C$2:$K$51,4,0),IF(AND(AU2="VBMS",M2="DEV"),VLOOKUP(C2,TeamDigitAssignments!$C$2:$K$51,3,0),IF(AND(AU2="VBMS",M2="EVD"),VLOOKUP(C2,TeamDigitAssignments!$C$2:$K$51,3,0),IF(AND(AU2="VBMS",M2="All Evidence Received, Not RFD"),VLOOKUP(C2,TeamDigitAssignments!$C$2:$K$51,3,0),IF(AND(M2="DEV",AT2<>"FDC",AU2<>"VBMS"),VLOOKUP(C2,TeamDigitAssignments!$C$2:$K$51,2,0),IF(AND(M2="EVD",AT2<>"FDC",AU2<>"VBMS"),VLOOKUP(C2,TeamDigitAssignments!$C$2:$K$51,2,0),IF(AND(M2="All Evidence Received, Not RFD",AT2<>"FDC",AU2<>"VBMS"),VLOOKUP(C2,TeamDigitAssignments!$C$2:$K$51,2,0),IF(M2="DEC",VLOOKUP(C2,TeamDigitAssignments!$C$2:$K$51,5,0),IF(M2="WRK",VLOOKUP(C2,TeamDigitAssignments!$C$2:$K$51,8,0),IF(M2="AWD",VLOOKUP(C2,TeamDigitAssignments!$C$2:$K$51,8,0),IF(M2="AUT",VLOOKUP(C2,TeamDigitAssignments!$C$2:$K$51,9,0)))))))))))))))))
    Reply With QuoteRegister To Reply
  4. 06-25-2013, 10:06 AM #4
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,454

    Re: Macro String too long

    OK, try it this way. It's also a little bit more readable.

    Please Login or Register  to view this content.

    As an alternative, you could consider renaming the sheet TeamDigitAssignments to something shorter, say TDA, and/or defining TeamDigitAssignments!$C$2:$K$51 as a Named (or Dynamic Named) Range. In fact, you could adopt that approach ans still build the formula up as above.


    Regards, TMS
    Reply With QuoteRegister To Reply
  5. 06-25-2013, 10:21 AM #5
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,454

    Re: Macro String too long

    If you define the Named Range TDA (TeamDigitAssignments!$C$2:$K$51), your formula becomes:

    =IF(AND(AT2="FDC",AU2="VBMS",M2="DEV"),VLOOKUP(C2,TDA,4,0),IF(AND(AT2="FDC",AU2="VBMS",M2="EVD"),VLOOKUP(C2,TDA,4,0),IF(AND(AT2="FDC",AU2="VBMS",M2="All Evidence Received, Not RFD"),VLOOKUP(C2,TDA,4,0),IF(AND(AT2="FDC",M2="DEV"),VLOOKUP(C2,TDA,4,0),IF(AND(AT2="FDC",M2="EVD"),VLOOKUP(C2,TDA,4,0),IF(AND(AT2="FDC",M2="All Evidence Received, Not RFD"),VLOOKUP(C2,TDA,4,0),IF(AND(AU2="VBMS",M2="DEV"),VLOOKUP(C2,TDA,3,0),IF(AND(AU2="VBMS",M2="EVD"),VLOOKUP(C2,TDA,3,0),IF(AND(AU2="VBMS",M2="All Evidence Received, Not RFD"),VLOOKUP(C2,TDA,3,0),IF(AND(M2="DEV",AT2<>"FDC",AU2<>"VBMS"),VLOOKUP(C2,TDA,2,0),IF(AND(M2="EVD",AT2<>"FDC",AU2<>"VBMS"),VLOOKUP(C2,TDA,2,0),IF(AND(M2="All Evidence Received, Not RFD",AT2<>"FDC",AU2<>"VBMS"),VLOOKUP(C2,TDA,2,0),IF(M2="DEC",VLOOKUP(C2,TDA,5,0),IF(M2="WRK",VLOOKUP(C2,TDA,8,0),IF(M2="AWD",VLOOKUP(C2,TDA,8,0),IF(M2="AUT",VLOOKUP(C2,TDA,9,0)))))))))))))))))

    And then the code is:

    Please Login or Register  to view this content.


    Regards, TMS
    Reply With QuoteRegister To Reply
  6. 06-25-2013, 10:42 AM #6
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Macro String too long

    Isn't that equivalent to ...

    Please Login or Register  to view this content.
    Last edited by shg; 06-25-2013 at 10:44 AM.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  7. 06-25-2013, 10:47 AM #7
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Macro String too long

    Or, for that matter,

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 06-25-2013, 11:01 AM #8
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,454

    Re: Macro String too long

    @shg: OK, go on, let me do all the hard work and then jump in at the end and tidy up the loose ends

    TMS
    Reply With QuoteRegister To Reply
  9. 06-25-2013, 11:26 AM #9
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Macro String too long

    OK, that was funny ...

    However, there's surely a better way to do things like this (i.e., sparse matches -- not all variables are used in each case) that I can't quite get my brain around -- a way to expose the logic in a table and use some kind of a match function.

    EDIT: Like the criteria table for Advanced filter -- know what I mean?
    Last edited by shg; 06-25-2013 at 11:30 AM.
    Reply With QuoteRegister To Reply
  10. 06-25-2013, 11:32 AM #10
    sam8114 is offline
    Registered User
    Join Date
    06-24-2013
    Location
    SC
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Macro String too long

    TMShucks your reply worked! Thank you so much!
    Reply With QuoteRegister To Reply
  11. 06-25-2013, 12:17 PM #11
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,454

    Re: Macro String too long

    @sam8114: You're welcome. Thanks for the rep.


    @shg: well, I didn't understand "sparse matches" but the Advanced Filter criteria table makes sense ... just how to apply it? I was happy just to get the cell populated; not so bothered about making it more efficient ... another question for another day. Personally, I'd be inclined to give your shorter formulae a go.

    Another less sophisticated way of shortening it would be to put, for example, DEV, EVD and All Evidence Received into a list and then use COUNTIF to determine if any one of them is present. So, =COUNTIF(TheList, M2)>0 to give True or False (untested).


    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1