CODEWITHSUNDEEP
vbaexcel
Graham Chandler
Graham Chandler

Reputation: 193

excel vba: line break in code

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

Upvotes: 1

Views: 3374

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

Line breaks don't work like that.

You need something like:

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

Upvotes: 7

Related Questions