none
How to break an IF formula into multiple lines RRS feed

  • Question

  • I know this is probably a simply answered question, but I've search and tried multiple things, but I continue to receive errors in VBA.

    I would like to break the following statement into multiple lines:

    ActiveCell.Formula = "=IF(RC[-24]=""Bill"",ROUNDUP(RC[-1]/2,0),(IF(RC[-24]=""eBill"",ROUNDUP(RC[-1]/1,0),0))"

    I would like to break it up into two lines as follows:

    ActiveCell.Formula = "=IF(RC[-24]=""Bill"",ROUNDUP(RC[-1]/2,0),

       (IF(RC[-24]=""eBill"",ROUNDUP(RC[-1]/1,0),0))"

    I have tried multiple combinations of ampersands and underscores, but nothing has been successful.

    Any help would be greatly appreciated.

    Thanks!

    Sunday, December 22, 2013 9:34 PM

Answers

  • Thanks Hans.  I believe I tried that previously, but I did it again and I receive an error that states "Expected: line number or label or statement or end of statement"

    Ensure you have a space then the underscore at the end of the first line.

    Then position the cursor immediately after the underscore at the end of the first line and press Enter.

    If it creates a blank line between the 2 lines then delete that line. (If it does not then the Line Feed between the lines was corrupt. Not a common problem but sometimes this can occur when editing existing code and produces the error you describe.)

    Then position the cursor immediately after the last character in the second line and press Enter.

    Test and see if the problem still exists.


    Regards, OssieMac

    • Marked as answer by Alex Bernauer Monday, December 23, 2013 5:41 PM
    Monday, December 23, 2013 3:18 AM

All replies

  • Although it's not required, I'd use FormulaR1C1 to specify explicitly that you're setting an R1C1-style formula.

    You can use

        ActiveCell.FormulaR1C1 = "=IF(RC[-24]=""Bill"",ROUNDUP(RC[-1]/2,0)," & _
            "IF(RC[-24]=""eBill"",ROUNDUP(RC[-1]/1,0),0))"

    to break the instruction into two lines.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, December 22, 2013 9:59 PM
  • Thanks Hans.  I believe I tried that previously, but I did it again and I receive an error that states "Expected: line number or label or statement or end of statement"

    Thanks again for the help.

    Sunday, December 22, 2013 10:04 PM
  • The code works fine for me if I place it in a macro:

    Sub Test()
        ActiveCell.FormulaR1C1 = "=IF(RC[-24]=""Bill"",ROUNDUP(RC[-1]/2,0)," & _
            "IF(RC[-24]=""eBill"",ROUNDUP(RC[-1]/1,0),0))"
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, December 22, 2013 10:34 PM
  • Thanks Hans.  I believe I tried that previously, but I did it again and I receive an error that states "Expected: line number or label or statement or end of statement"

    Ensure you have a space then the underscore at the end of the first line.

    Then position the cursor immediately after the underscore at the end of the first line and press Enter.

    If it creates a blank line between the 2 lines then delete that line. (If it does not then the Line Feed between the lines was corrupt. Not a common problem but sometimes this can occur when editing existing code and produces the error you describe.)

    Then position the cursor immediately after the last character in the second line and press Enter.

    Test and see if the problem still exists.


    Regards, OssieMac

    • Marked as answer by Alex Bernauer Monday, December 23, 2013 5:41 PM
    Monday, December 23, 2013 3:18 AM
  • Thanks Hans & OssieMac.  That did the trick - I did not have a space between the ampersand and the underscore previously.
    Monday, December 23, 2013 5:41 PM