+ Reply to Thread
Results 1 to 6 of 6

Can't make Line Continuation work in Nested IF AND statement

Hybrid View

  1. 05-30-2018, 06:24 PM #1
    TrueFangz
    TrueFangz is offline
    Registered User
    Join Date
    08-07-2015
    Location
    Washington
    MS-Off Ver
    365, v1808 32bit
    Posts
    59

    Can't make Line Continuation work in Nested IF AND statement

    I am working on a project that is basically a calculation tool. I have two drop down lists that I'm pulling info from, and need to do a specific calculation based on the selection between the two drop down lists.

    Basic Scenario is if cell D13 equals "something" and H13 equals "Something" then do some math, but if D13 equals "something else" and H13 equals "Something else" then do different math.

    My starting code is as follows (which works)

    Range("D20").Select
Selection.Formula = "=IF(AND(D13=""something"",H13=""something""),(D15*H15)*Sheet2!B2+D17*Sheet2!H2+H17*Sheet2!H3+K17*Sheet2!H4,IF(AND(D13=""something"",H13=""somethingelse""),(D15*H15)*Sheet2!C2+D17*Sheet2!H2+H17*Sheet2!H3+K17*Sheet2!H4,IF(AND(D13=""somethingelse"",H13=""something""),(D15*H15)*Sheet2!D2+D17*Sheet2!H2+H17*Sheet2!H3+K17*Sheet2!H4,IF(AND(D13=""Somethingelse"",H13=""somethingelse""),(D15*H15)*Sheet2!E2+D17*Sheet2!H2+H17*Sheet2!H3+K17*Sheet2!H4,""""))))"
    I tried entering line breaks as follows, but keep getting error messages no matter where I try putting the underscore:

    Range("D20").Select
Selection.Formula = "=IF(AND(D13=""something"",H13=""something""),(D15*H15)*Sheet2!B2+D17*Sheet2!H2+H17*Sheet2!H3+K17*Sheet2!H4, _
IF(AND(D13=""something"",H13=""somethingelse""),(D15*H15)*Sheet2!C2+D17*Sheet2!H2+H17*Sheet2!H3+K17*Sheet2!H4, _
IF(AND(D13=""somethingelse"",H13=""something""),(D15*H15)*Sheet2!D2+D17*Sheet2!H2+H17*Sheet2!H3+K17*Sheet2!H4, _
IF(AND(D13=""Somethingelse"",H13=""somethingelse""),(D15*H15)*Sheet2!E2+D17*Sheet2!H2+H17*Sheet2!H3+K17*Sheet2!H4,""""))))"
    The error usually points to the second "IF" saying "Compile Error: Expected: end of statement"

    I know this code is long and ugly, and I need to clone it several more times for the worksheet including &IF scenarios, so I certainly am willing to simplify it if possible. I am not much of a coder or programmer, and have been relying on the Excel Macro Recorder to generate my VBA codes.

    Thanks in advance for your patience with me and your help.
    Reply With QuoteRegister To Reply
  2. 05-30-2018, 06:36 PM #2
    gmr4evr1
    gmr4evr1 is offline
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Can't make Line Continuation work in Nested IF AND statement

    TrueFangz,
    I'm not sure about this because I've not done a formula in this manner, but, because there is only a quotation at the beginning and the end of the line break formula, could it be including the space and underscore in the formula?
    Does each new line need to have quotes?
    I could be totally off base with this, but that is what I would look at if I were doing this.
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom
    Reply With QuoteRegister To Reply
  3. 05-30-2018, 06:37 PM #3
    TMS
    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,077

    Re: Can't make Line Continuation work in Nested IF AND statement

    Like this, maybe:

    Range("D20").Select
Selection.Formula = "=IF(AND(D13=""something"",H13=""something""),(D15*H15)*Sheet2!B2+D17*Sheet2!H2+H17*Sheet2!H3+K17*Sheet2!H4,IF(AND(D13=""something"",H13=""somethingelse""),(D15*H15)*Sheet2!C2+D17*Sheet2!H2+H17*Sheet2!H3+K17*Sheet2!H4,IF(AND(D13=""somethingelse"",H13=""something""),(D15*H15)*Sheet2!D2+D17*Sheet2!H2+H17*Sheet2!H3+K17*Sheet2!H4,IF(AND(D13=""Somethingelse"",H13=""somethingelse""),(D15*H15)*Sheet2!E2+D17*Sheet2!H2+H17*Sheet2!H3+K17*Sheet2!H4,""""))))"

' can become:
Range("D20").Formula = _
    "=IF(AND(D13=""something"",H13=""something""),(D15*H15)*Sheet2!B2+D17*Sheet2!H2+H17*Sheet2!H3+K17*Sheet2!H4," & _
    "IF(AND(D13=""something"",H13=""somethingelse""),(D15*H15)*Sheet2!C2+D17*Sheet2!H2+H17*Sheet2!H3+K17*Sheet2!H4," & _
    "IF(AND(D13=""somethingelse"",H13=""something""),(D15*H15)*Sheet2!D2+D17*Sheet2!H2+H17*Sheet2!H3+K17*Sheet2!H4," & _
    "IF(AND(D13=""Somethingelse"",H13=""somethingelse""),(D15*H15)*Sheet2!E2+D17*Sheet2!H2+H17*Sheet2!H3+K17*Sheet2!H4,""""))))"
    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
  4. 05-30-2018, 06:44 PM #4
    Winon
    Winon is offline
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Can't make Line Continuation work in Nested IF AND statement

    Hello TrueFangz,

    Edit: Post deleted since TMS beat me to it.

    Regards.
    Last edited by Winon; 05-30-2018 at 06:47 PM.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
    Reply With QuoteRegister To Reply
  5. 05-30-2018, 07:17 PM #5
    TrueFangz
    TrueFangz is offline
    Registered User
    Join Date
    08-07-2015
    Location
    Washington
    MS-Off Ver
    365, v1808 32bit
    Posts
    59

    Re: Can't make Line Continuation work in Nested IF AND statement

    Thank you both for your replies. @TMS, this appears to work beautifully, thanks so much, hopefully this will help me produce a nice clean code.
    Reply With QuoteRegister To Reply
  6. 05-30-2018, 07:42 PM #6
    TMS
    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,077

    Re: Can't make Line Continuation work in Nested IF AND statement

    You're welcome. Thanks for the rep.
    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)

Similar Threads

  1. [SOLVED] i have an if then statement that works, how to make it work as a with or case statement
    By dmcgov in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 06-03-2016, 03:34 PM
  2. [SOLVED] Cannot make nested if's work
    By rfcomm2k in forum Excel General
    Replies: 3
    Last Post: 12-19-2014, 09:53 AM
  3. [SOLVED] nested if formula that i cant sem to make work
    By fordtough68 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-19-2013, 11:44 AM
  4. Nested IF statement and line graph.
    By pinksnyper in forum Excel General
    Replies: 0
    Last Post: 12-22-2010, 03:55 PM
  5. Line continuation
    By Wedge120 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-06-2010, 11:48 PM
  6. [SOLVED] Why doesn't this nested IF statement work?
    By Brandoni in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-19-2006, 05:30 PM
  7. What is the limit of the line continuation in VB
    By mmamin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-01-2005, 08:05 AM

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