adding a formula across the entire row and then performing operation on that.

[rajm11]

Firstly hello and thanks all for nice and working solutions across the forum.

now here is my question.
I have more than 50,000 rows of data
I have learned how to insert a blank row after changing the data in particular row ( that is data changes from day 01 jan 2013 to day 01 feb 2013)

here is what I cant do
Now in this existing blank row I want do sum of rows above and put that as formula
again insert another blank row and divide this sum by 86400 and format cell as time across the row
for last three columns formula is =/1000 instead of 86400

end

till now I have got formula from this forum itself as

Dim lLastRow As Long
With Sheets("Sheet1") 'put your sheet name here
lLastRow = .Range("L" & .Rows.Count).End(xlUp).Row
.Range("L" & lLastRow + 1).Formula = "=Sum(L2:L" & lLastRow & ")"
End With
But this insert formula only for one column..

Can any one help ?

 

[Fishboy]

Firstly hello and thanks all for nice and working solutions across the forum.

now here is my question.
I have more than 50,000 rows of data
I have learned how to insert a blank row after changing the data in particular row ( that is data changes from day 01 jan 2013 to day 01 feb 2013)

here is what I cant do
Now in this existing blank row I want do sum of rows above and put that as formula
again insert another blank row and divide this sum by 86400 and format cell as time across the row
for last three columns formula is =/1000 instead of 86400

end

till now I have got formula from this forum itself as

Dim lLastRow As Long
With Sheets("Sheet1") 'put your sheet name here
lLastRow = .Range("L" & .Rows.Count).End(xlUp).Row
.Range("L" & lLastRow + 1).Formula = "=Sum(L2:L" & lLastRow & ")"
End With
But this insert formula only for one column..

Can any one help ?

Hi rajm11,

What columns do you want this applied to?

 

[rajm11]

Hello fishboy

I want this to be applied across starting from s to BC i.e. s,t ,u,v,w,x,y,z........... till bc

is that possible

I came across a very lame solution is the sense that I literally wrote a code for each column,
now even to paste the macro here its huge
but here is snippet


Sub sumsofdataq12013()
Dim Rng As Range, r As Range
Dim rng1 As Range, a As Range
Dim Rng2 As Range, b As Range
Dim Rng3 As Range, c As Range
Dim Rng4 As Range, d As Range
Dim Rng5 As Range, e As Range
Dim Rng6 As Range, f As Range
Dim Rng7 As Range, g As Range
Dim Rng8 As Range, h As Range
Dim Rng9 As Range, i As Range
Dim Rng10 As Range, j As Range


Dim Rng11 As Range, k As Range


Dim Rng12 As Range, l As Range


Dim Rng13 As Range, m As Range






Set Rng = Range("l2:l" & Range("l" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants)




For Each r In Rng.Areas
With r
.Cells(1, 1).Offset(.Rows.Count).Formula = "=sum(" & .Address & ", )/86400"
End With
Next
Set rng1 = Range("m2:m" & Range("m" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants)


For Each a In rng1.Areas
With a
.Cells(1, 1).Offset(.Rows.Count).Formula = "=sum(" & .Address & ", )/86400"
End With
Next
Set Rng2 = Range("n2:n" & Range("n" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants)


For Each b In Rng2.Areas
With b
.Cells(1, 1).Offset(.Rows.Count).Formula = "=sum(" & .Address & ", )/86400"

End With

 

[Fishboy]

Hello fishboy

I want this to be applied across starting from s to BC i.e. s,t ,u,v,w,x,y,z........... till bc

is that possible

I came across a very lame solution is the sense that I literally wrote a code for each column,
now even to paste the macro here its huge
but here is snippet

Sub sumsofdataq12013()
Dim Rng As Range, r As Range
Dim rng1 As Range, a As Range
Dim Rng2 As Range, b As Range
Dim Rng3 As Range, c As Range
Dim Rng4 As Range, d As Range
Dim Rng5 As Range, e As Range
Dim Rng6 As Range, f As Range
Dim Rng7 As Range, g As Range
Dim Rng8 As Range, h As Range
Dim Rng9 As Range, i As Range
Dim Rng10 As Range, j As Range


Dim Rng11 As Range, k As Range


Dim Rng12 As Range, l As Range


Dim Rng13 As Range, m As Range






Set Rng = Range("l2:l" & Range("l" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants)




For Each r In Rng.Areas
    With r
        .Cells(1, 1).Offset(.Rows.Count).Formula = "=sum(" & .Address & ", )/86400"
     End With
Next
Set rng1 = Range("m2:m" & Range("m" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants)


For Each a In rng1.Areas
    With a
        .Cells(1, 1).Offset(.Rows.Count).Formula = "=sum(" & .Address & ", )/86400"
           End With
Next
Set Rng2 = Range("n2:n" & Range("n" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants)


For Each b In Rng2.Areas
    With b
        .Cells(1, 1).Offset(.Rows.Count).Formula = "=sum(" & .Address & ", )/86400"
        
    End With

Right, if i have understood correctly, try this:

Sub ApplyFormulas()
' Defines variables
Dim Cell As Range, cRange As Range, LastRow As Long
    ' Sets check range as S1:BC1
    Set cRange = Range("S1:BC1")
        ' For each column (cell) in check range
        For Each Cell In cRange
            ' Work out the last row of the current column
            LastRow = ActiveSheet.Cells(Rows.Count, Cell.Column).End(xlUp).Row
                ' Apply the formula which sums row 2 to the last row of the current column
                Cells(LastRow + 1, Cell.Column).Formula = "=SUM(" & Cells(2, Cell.Column) & ":" & Cells(LastRow, Cell.Column) & ")"
        ' Move to next column along
        Next Cell
End Sub

 

[rajm11]

Hmmm

I tried that but it gives me application or object defined error also if I want to put in /86400 in formula where do put it before last " ?

 

[Fishboy]

Hmmm

I tried that but it gives me application or object defined error also if I want to put in /86400 in formula where do put it before last " ?

Hmm, try this instead. I have higlighted my changed in red.

Sub ApplyFormulas()
' Defines variables
Dim Cell As Range, cRange As Range, LastRow As Long
    ' Sets check range as S1:BC1
    Set cRange = Range("S1:AA1")
        ' For each column (cell) in check range
        For Each Cell In cRange
            ' Work out the last row of the current column
            LastRow = ActiveSheet.Cells(Rows.Count, Cell.Column).End(xlUp).Row
                ' Apply the formula which sums row 2 to the last row of the current column
                Cells(LastRow + 1, Cell.Column).Formula = "=SUM(" & Cells(2, Cell.Column).Address & ":" & Cells(LastRow, Cell.Column).Address & ")/86400"
        ' Move to next column along
        Next Cell
End Sub

 

[rajm11]

very strangel;y nothing happens very strange indeed.

 

[Fishboy]

very strangel;y nothing happens very strange indeed.

Hmmm, beyong me forgetting to change the AA1 reference back to BC1 in my last example I don't have the same issue at this end.

HERE is my simplified test document with the macro working as described.

Have a go and see if it actually does what you need,

 

[rajm11]

Aha,

Yes it works fine in your example document without any problem, but when I did insert a blank row between say row 5 and 6 and then ran it , it gave me the result in only few of the columns (in your given sheet it gave results in column v, column AK, column AT) with perfection and then again at the end with sum of rows 2 to last one including blank one....

 

[Jonmo1]

till now I have got formula from this forum itself as

Dim lLastRow As Long
With Sheets("Sheet1") 'put your sheet name here
lLastRow = .Range("L" & .Rows.Count).End(xlUp).Row
.Range("L" & lLastRow + 1).Formula = "=Sum(L2:L" & lLastRow & ")"
End With
But this insert formula only for one column..

To make that code put the formula in columns S:BC (as stated in post #3)

I want this to be applied across starting from s to BC i.e. s,t ,u,v,w,x,y,z........... till bc

Then add another row with formula dividing previous row by 8640, formatted as time.

Try

Dim lLastRow As Long
With Sheets("Sheet1") 'put your sheet name here
    lLastRow = .Range("L" & .Rows.Count).End(xlUp).Row + 1
    .Range(.Cells(lLastRow, "S"), .Cells(lLastRow, "BC")).FormulaR1C1 = "=Sum(R2C:R[-1]C)"
    .Range(.Cells(lLastRow + 1, "S"), .Cells(lLastRow + 1, "BC")).FormulaR1C1 = "=R[-1]C/8640"
    .Range(.Cells(lLastRow + 1, "S"), .Cells(lLastRow + 1, "BC")).NumberFormat = "hh:mm:ss"
End With