none
carry formula in vba to next line RRS feed

  • Question

  • Hello.  I am working on a spreadsheet with vba, etc.  I am using a formula (see below) in the vba macro but would like to split the length of it into 2 lines.  Currently it is one long line.  How or what needs to be done to put it on two lines correctly so as to shorten the length to a more managable view?

    Thanks....John

    Range("C38").Value = [SUMIFS(Transaction!$O:$O, Transaction!$B:$B, ">=1/1/2015", Transaction!$B:$B, "<=1/31/2015",Transaction!$C:$C, "BTA Dues", Transaction!$D:$D, "Payment", Transaction!$M:$M, B38, Transaction!$P:$P, "Income")]

    Friday, January 23, 2015 4:20 PM

Answers

  • I don't think you can do that with the [ ] syntax. You could use EVALUATE instead, but then the argument becomes a string, and you have to double the quotes within the string:

        Range("C38").Value = Evaluate("SUMIFS(Transaction!$O:$O, " & _
            "Transaction!$B:$B, "">=1/1/2015"", Transaction!$B:$B, ""<=1/31/2015"", " & _
            "Transaction!$C:$C, ""BTA Dues"", Transaction!$D:$D, ""Payment"", " & _
            "Transaction!$M:$M, B38, Transaction!$P:$P, ""Income"")")


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

    • Marked as answer by johnboy0276 Friday, January 23, 2015 5:25 PM
    Friday, January 23, 2015 5:15 PM

All replies

  • I don't think you can do that with the [ ] syntax. You could use EVALUATE instead, but then the argument becomes a string, and you have to double the quotes within the string:

        Range("C38").Value = Evaluate("SUMIFS(Transaction!$O:$O, " & _
            "Transaction!$B:$B, "">=1/1/2015"", Transaction!$B:$B, ""<=1/31/2015"", " & _
            "Transaction!$C:$C, ""BTA Dues"", Transaction!$D:$D, ""Payment"", " & _
            "Transaction!$M:$M, B38, Transaction!$P:$P, ""Income"")")


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

    • Marked as answer by johnboy0276 Friday, January 23, 2015 5:25 PM
    Friday, January 23, 2015 5:15 PM
  • Thank you for the response.  The [] are not necessary if Evaluate works and from what I can tell, it does.  Made live just a bit simpler.  Thanks again.

    ...John

    Friday, January 23, 2015 5:25 PM