Excel 2013 VBA add new line to Formula

Question

With my VBA Code i add a new line to a table. In the last row of the table i have a =Sum(SumIf(...)) Formula, this row will jump one row down if i add the new line.

So now i want to add the added line to the Formula.

This is my Code so far:

Private Sub CommandButton1_Click()
    Dim lastrow As Long
    Dim i As Integer

    lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1

    i = 4
    Do Until i = lastrow
        calc = "SUMIF($K$3:$K$19,N" & i & ",$L$3:$L$19))"
        ActiveSheet.Range("N" & lastrow + 1 & ":BI" & lastrow + 1).Formula = "=SUM(SUMIF($K$3:$K$19,N3,$L$3:$L$19)," & calc
        i = i + 1
    Loop
End Sub

In fact it works fine, but in the End the Formula looks like this:

=Sum(SumIf($K$3:$K$19,N3,$L$3:$L$19),SumIF($K$3:$K$19,N21,$L$3:$L$19))

What do i have to add to the code that every row will be added to the Formula?

Hope someone can help me. I am new to VBA.

EDIT:

The Worksheet shows all assignments and the working progress. With a CommandButton i add a new assignment to the table. So right now my table has 20 rows, in row 21 is the =SUM(SUMIF(..)) - Formula. If i add a new assignment the formula goes to row 22 and in row 21 the new assignment will be shown.

With my Code i want that every row will be added to the formula. Right now the formula goes from N3 to N20. If i add the new line the N21 will not be added to the formula. With my code above it only shows the first and the last row.

In fact i want that the formula will look like this:

=SUM(SUMIF($K$3:$K$19,N3,$L$3:$L$19),SUMIF($K$3:$K$19,N4,$L$3:$L$19) ... SUMIF($K$3:$K$19,N21,$L$3:$L$19))

This is my table hope it will help

Welcome to [so]! It's unclear to me what you're trying to do - but I nonetheless suspect there's an easier way. Your question would be more clear if you included an explanation of what you have (ie., sample data), what you're trying to do, and what it should be like in the end. — ashleedawg, Jun 14 '18 at 06:26
@ashleedawg see my edit part. hope i could explain it now that it is clear what i want — diem_L, Jun 14 '18 at 06:48

score 0 · Accepted Answer · answered Jun 14 '18 at 06:58

0

This will solve your problem. You need to assign a value to the variable "calc" before your loop. Then add to its value inside the loop with another statement. Finally, do not put the formula into the cells' values until after you have exited the loop and add a final parentheses to the formula.

Dim lastrow As Long
Dim i As Integer

lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1

i = 4
calc = "=SUM(SUMIF($K$3:$K$19,N3,$L$3:$L$19),"
Do Until i = lastrow
    calc = calc & "SUMIF($K$3:$K$19,N" & i & ",$L$3:$L$19),"
    i = i + 1
Loop

ActiveSheet.Range("N" & lastrow + 1 & ":BI" & lastrow + 1).Value = calc & ")"

answered Jun 14 '18 at 06:58

The Dude

314
2
7

Thank you Thank you Thank you .. you saved me. This is brilliant. – diem_L Jun 14 '18 at 07:01
1

Note: Don't use `Integer` for row counting, Excel has more rows than `Integer` can handle. It is recommended [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) in VBA since there is no benefit in using `Integer` at all. – Pᴇʜ Jun 14 '18 at 07:06

Excel 2013 VBA add new line to Formula

1 Answers1