Sum to the last row in vba
 

O.K. this code is working to put in a formula that sums a certain number of
rows every time the if statement is true, however I need to alter the code to
sum a variable number of rows, i.e. one cell down : end (xldown). Is that
possible?

Sub cleanup2()
Worksheets("Raw Data").Activate
With ActiveSheet
For i = 10 To 479
If Cells(i, 13) = "1" And Cells(i + 1, 13) = "" Then
Cells(i, 20).Select
ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[7]C)"
End If
Next
End With

End Sub

THanks in advance,
Holly

Sum to the last row in vba
 

n = Cells(Rows.Count, "M").End(xlUp).Row
For i = 10 to n
--
Gary''s Student - gsnu200739


"hnyb1" wrote:

O.K. this code is working to put in a formula that sums a certain number of
rows every time the if statement is true, however I need to alter the code to
sum a variable number of rows, i.e. one cell down : end (xldown). Is that
possible?

Sub cleanup2()
Worksheets("Raw Data").Activate
With ActiveSheet
For i = 10 To 479
If Cells(i, 13) = "1" And Cells(i + 1, 13) = "" Then
Cells(i, 20).Select
ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[7]C)"
End If
Next
End With

End Sub

THanks in advance,
Holly

Sum to the last row in vba
 

Thanks for the response, however I don't think this will work as I don't have
a continuous column of data. Please see the following ex:

col M col N col O col P col Q col R col S col T

1 21 28 16 0 2 0
x
x
x
1 16 21 2 0 1 2
x
x
x
x
x

So, in column T I need to sum the x's at each row that has a 1 in column M.
Hope that is slightly clearer than mud...

Thanks,
Holly

"hnyb1" wrote:

O.K. this code is working to put in a formula that sums a certain number of
rows every time the if statement is true, however I need to alter the code to
sum a variable number of rows, i.e. one cell down : end (xldown). Is that
possible?

Sub cleanup2()
Worksheets("Raw Data").Activate
With ActiveSheet
For i = 10 To 479
If Cells(i, 13) = "1" And Cells(i + 1, 13) = "" Then
Cells(i, 20).Select
ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[7]C)"
End If
Next
End With

End Sub

THanks in advance,
Holly