VBA Line continuation

[Kinseres]

Hi All

I'm wondering if anyone can help me with the correct placement of line continuation in VBA. I have a piece of code that essentially just adds up the results of a number of sumifs, however, I cannot seem to find where in the individual sumifs formulas the line continuation needs to be placed to avoid syntax errors.

Any help at all would be appreciated!

WC = Sheets("Export").Range("A1").Cells(lrow + 1, 1).Value
Sheets("Export").Range("B1").Cells(lrow + 1, 1).Formula = "SUMIFS('Raw Data'!$T:$T,'Raw Data'!$D:$D,"">=""&" & WC & ",'Raw Data'!$D:$D,""<""&" & WC & "+7,'Raw Data'!$I:$I,""Ward 1"")+ _
SUMIFS('Raw Data'!$J:$J,'Raw Data'!$D:$D,"">=""&" & WC & ",'Raw Data'!$D:$D,""<""&" & WC & "+7,'Raw Data'!$I:$I,""Ward 2"")+ _
SUMIFS('Raw Data'!$J:$J,'Raw Data'!$D:$D,"">=""&" & WC & ",'Raw Data'!$D:$D,""<""&" & WC & "+7,'Raw Data'!$I:$I,""Ward 3"")+ _
SUMIFS('Raw Data'!$J:$J,'Raw Data'!$D:$D,"">=""&" & WC & ",'Raw Data'!$D:$D,""<""&" & WC & "+7,'Raw Data'!$I:$I,""Ward 4"")+ _
SUMIFS('Raw Data'!$J:$J,'Raw Data'!$D:$D,"">=""&" & WC & ",'Raw Data'!$D:$D,""<""&" & WC & "+7,'Raw Data'!$I:$I,""Ward 5"")+ _
SUMIFS('Raw Data'!$J:$J,'Raw Data'!$D:$D,"">=""&" & WC & ",'Raw Data'!$D:$D,""<""&" & WC & "+7,'Raw Data'!$I:$I,""Ward 6"")+ _
SUMIFS('Raw Data'!$J:$J,'Raw Data'!$D:$D,"">=""&" & WC & ",'Raw Data'!$D:$D,""<""&" & WC & "+7,'Raw Data'!$I:$I,""Ward 7"")"

 

[RoryA]

You can't put it inside the formula string. You have to terminate the string and then continue it using & as well as the line continuation:

Sheets("Export").Range("B1").Cells(lrow + 1, 1).Formula = "SUMIFS('Raw Data'!$T:$T,'Raw Data'!$D:$D,"">=""&" & WC & ",'Raw Data'!$D:$D,""<""&" & WC & "+7,'Raw Data'!$I:$I,""Ward 1"")+" &  _
"SUMIFS('Raw Data'!$J:$J,'Raw Data'!$D:$D,"">=""&" & WC & ",'Raw Data'!$D:$D,""<""&" & WC & "+7,'Raw Data'!$I:$I,""Ward 2"")+" &  _
"SUMIFS('Raw Data'!$J:$J,'Raw Data'!$D:$D,"">=""&" & WC & ",'Raw Data'!$D:$D,""<""&" & WC & "+7,'Raw Data'!$I:$I,""Ward 3"")+" &  _
"SUMIFS('Raw Data'!$J:$J,'Raw Data'!$D:$D,"">=""&" & WC & ",'Raw Data'!$D:$D,""<""&" & WC & "+7,'Raw Data'!$I:$I,""Ward 4"")+" &  _
"SUMIFS('Raw Data'!$J:$J,'Raw Data'!$D:$D,"">=""&" & WC & ",'Raw Data'!$D:$D,""<""&" & WC & "+7,'Raw Data'!$I:$I,""Ward 5"")+" &  _
"SUMIFS('Raw Data'!$J:$J,'Raw Data'!$D:$D,"">=""&" & WC & ",'Raw Data'!$D:$D,""<""&" & WC & "+7,'Raw Data'!$I:$I,""Ward 6"")+" &  _
"SUMIFS('Raw Data'!$J:$J,'Raw Data'!$D:$D,"">=""&" & WC & ",'Raw Data'!$D:$D,""<""&" & WC & "+7,'Raw Data'!$I:$I,""Ward 7"")"

 

[Kinseres]

awesome! cheers man

 

[Peter_SSs]

FWIW, you could reduce the need for line continuations if you changed to a formula like this.

Sheets("Export").Range("B1").Cells(lrow + 1, 1).Formula = "=SUMIFS('Raw Data'!$T:$T,'Raw Data'!$D:$D,"">=""&" & WC & ",'Raw Data'!$D:$D,""<""&" & WC & "+7,'Raw Data'!$I:$I,""Ward 1"")+" & _
"SUM(SUMIFS('Raw Data'!$J:$J,'Raw Data'!$D:$D,"">=""&" & WC & ",'Raw Data'!$D:$D,""<""&" & WC & "+7,'Raw Data'!$I:$I,{""Ward 2"",""Ward 3"",""Ward 4"",""Ward 5"",""Ward 6"",""Ward 7""}))"

 

[Kinseres]

Hi Peter,

Thanks, I had considered using a similar formula to this, but occasionally I need to discount 1 or more of the wards from the sum, so I wanted each of them distinctly separate in my coding so I could 'deselect' them using a notation mark.

Thanks for the thought though, greatly appreciated

 

[Peter_SSs]

Fair enough.