excel vba: line break in code

Question

I have a rather lengthy formula in excel vba that I'm being forced to break into multiple lines. I've tried inserting a " _" but I'm getting the following error:

"Compile error: Expected: end of statement"

I've looked over this code several times and cant seem to find what is driving this error. If someone could look over the code below and help me figure this out I'd greatly appreciate it. Thanks in advance.

        If InStr(1, lineitem.Value, "Excess MO") > 0 And InStr(1, lineitem.Value, "AOS") > 0 Then
        lineitem.Offset(0, 16).Formula = "=SUMPRODUCT(COUNTIFS(INDIRECT(""'""&R2C3&"" Claims'!B:B""),R[0]C[-19],INDIRECT(""'""&R2C3&"" Claims'!C:C""),R[0]C[-17],INDIRECT(""'""&R2C3&"" Claims'!E:E""),CHOOSE({1;2},R[0]C[-15],R[0]C[-14]),INDIRECT(""'""&R2C3&"" Claims'!P:P""),"">""&R5C3,INDIRECT(""'""&R2C3&"" Claims'!H:H""),""<>""&""NS""))- _
        SUMPRODUCT(COUNTIFS(INDIRECT(""'""&R2C3&"" Claims'!B:B""),R[0]C[-19],INDIRECT(""'""&R2C3&"" Claims'!C:C""),R[0]C[-17],INDIRECT(""'""&R2C3&"" Claims'!E:E""),CHOOSE({1;2},R[0]C[-15],R[0]C[-14]),INDIRECT(""'""&R2C3&"" Claims'!P:P""),"">""&R5C3,INDIRECT(""'""&R2C3&"" Claims'!H:H""),""<>""&""NS"",INDIRECT(""'""&R2C3&"" Claims'!G:G""),R2C5:R2C27))- _
        SUMPRODUCT(COUNTIFS(INDIRECT(""'""&R2C3&"" Claims'!B:B""),R[0]C[-19],INDIRECT(""'""&R2C3&"" Claims'!C:C""),R[0]C[-17],INDIRECT(""'""&R2C3&"" Claims'!E:E""),CHOOSE({1;2},R[0]C[-15],R[0]C[-14]),INDIRECT(""'""&R2C3&"" Claims'!P:P""),"">""&R5C3,INDIRECT(""'""&R2C3&"" Claims'!H:H""),""<>""&""NS"",INDIRECT(""'""&R2C3&"" Claims'!G:G""),R5C5:R5C54))"
        lineitem.Offset(0, 16).Value = lineitem.Offset(0, 16).Value
        lineitem.Offset(0, 17).Formula = "=SUMPRODUCT(COUNTIFS(INDIRECT(""'""&R3C3&"" Claims'!B:B""),R[0]C[-20],INDIRECT(""'""&R3C3&"" Claims'!C:C""),R[0]C[-18],INDIRECT(""'""&R3C3&"" Claims'!E:E""),CHOOSE({1;2},R[0]C[-16],R[0]C[-15]),INDIRECT(""'""&R3C3&"" Claims'!P:P""),"">""&R5C3,INDIRECT(""'""&R3C3&"" Claims'!H:H""),""<>""&""NS""))- _
        SUMPRODUCT(COUNTIFS(INDIRECT(""'""&R3C3&"" Claims'!B:B""),R[0]C[-20],INDIRECT(""'""&R3C3&"" Claims'!C:C""),R[0]C[-18],INDIRECT(""'""&R3C3&"" Claims'!E:E""),CHOOSE({1;2},R[0]C[-16],R[0]C[-15]),INDIRECT(""'""&R3C3&"" Claims'!P:P""),"">""&R5C3,INDIRECT(""'""&R3C3&"" Claims'!H:H""),""<>""&""NS"",INDIRECT(""'""&R3C3&"" Claims'!G:G""),R2C5:R2C27))- _
        SUMPRODUCT(COUNTIFS(INDIRECT(""'""&R3C3&"" Claims'!B:B""),R[0]C[-20],INDIRECT(""'""&R3C3&"" Claims'!C:C""),R[0]C[-18],INDIRECT(""'""&R3C3&"" Claims'!E:E""),CHOOSE({1;2},R[0]C[-16],R[0]C[-15]),INDIRECT(""'""&R3C3&"" Claims'!P:P""),"">""&R5C3,INDIRECT(""'""&R2C3&"" Claims'!H:H""),""<>""&""NS"",INDIRECT(""'""&R3C3&"" Claims'!G:G""),R5C5:R5C54))"
        lineitem.Offset(0, 17).Value = lineitem.Offset(0, 17).Value
        End If

Tim Williams · Accepted Answer · 2016-01-27 22:20:00Z

7

Line breaks don't work like that.

You need something like:

   fset(0, 16).Formula = "THISIS() + " & _
                         "REALLYLONG()"

answered Jan 27, 2016 at 22:20

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Graham Chandler Over a year ago

is it because I'm using spreadsheet formulas in vba? I don't normally use them

Dirk Reichel Over a year ago

You are inside a string and except for " it will ignore any syntax. This way you need to end the string to make the _ useable.

Elbert Villarreal Over a year ago

@GrahamChandler Remember to check the answer as correct when you get the right answer

Collectives™ on Stack Overflow

excel vba: line break in code

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related