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]

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....

I don't seem to get that issue either...

If I insert a blank row between rows 5 and 6, then run the macro I still get a formula in the "last row" of each column.

Excel 2010
	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC
1	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS	HEADERS
2	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10
3	10	10	10	10	10	10	10	10	10	0.000116	10	0.000116	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	0.000116	10	0.000116	10
4	10	10	10	0.000231	10	10	10	10	10		10		10	10	10	10	10	10	0.000231	10	10	10	10	10	10	10	10	0.000231	10	10	10	10	10		10		10
5	10	10	10		10	10	10	10	0.000347		10		0.000347	10	10	10	10	10		10	10	10	10	0.000347	10	10	10		10	10	10	10	0.000347		10		0.000347
6											0.000463				0.000463																				0.000463
7	10	10	10		10	10	10	10						10		10	10	10		10	10	10	10		10	10	10		10	10	10	10
8	10	0.000579	10		10	10	10	10						10		10	0.000579	10		10	10	10	10		10	0.000579	10		10	10	10	10
9	10		10		0.000694	10	10	10						10		10		10		0.000694	10	10	10		10		10		0.000694	10	10	10
10	10		0.00081			10	0.00081	10						10		10		0.00081			10	0.00081	10		10		0.00081			10	0.00081	10
11	10					10		10						0.000926		10					10		10		10					10		10
12	10					10		0.001042								10					10		0.001042		10					10		0.001042
13	0.001157					10										0.001157					10				0.001157					10
14						0.001273															0.001273									0.001273
Run Macro Here

 

[rajm11]

Sorry I was not clear the last row in my thinking is the row where I have blank row when information changes. May be let me clear my data set and what it entails.

year day of year shift other header1 other header1 other header1
1 2013 1feb 2013 4568 x y z ....
2 2013 1feb 2013 4562 x45 y34 z232 ....

3 BLANK ROW here I want the sum/86400 for all header above from shift (rows 1,2)
4 Blank row

5 2013 2 feb 2013 4562 x4 y3 z23 ....
6 2013 2 feb 2013 4562 x4 y3 z23 ....
7 2013 2 feb 2013 4562 x4 y3 z23 ....

8 Blank row here I want the sum/86400 for all header from shift (rows 5,6,7 )
9 Blank row

10 2013 3 feb 2013 4562 x4 y3 z23 ....
11 2013 3 feb 2013 4562 x4 y3 z23 ....
12 Blank row here I want the sum/86400 for all header from shift (rows 10,11)
13 Blank row
.
.
Unfortunately I cant print screen and put picture here , since first change of data occurs at row 616 then next is at 1136..... and I have more than 50,000 rows.

 

[rajm11]

Hello Jonmo1 thanks for posting the macro but it again gives the result in VERY last row at the end of document , where I am trying to insert formula when there is information change (I have blank cell after information change, this change in random i.e. I )... But I liked the number format part I have to borrow it for my working code.

 

[Fishboy]

Hello Jonmo1 thanks for posting the macro but it again gives the result in VERY last row at the end of document , where I am trying to insert formula when there is information change (I have blank cell after information change, this change in random i.e. I )... But I liked the number format part I have to borrow it for my working code.

OK, so to clarify (and we'll use my example above for visualisation) you want the result to go in the FIRST blank row and not on the LAST row. Is it still supposed to be a sum of the entire column or only as far as the cell with the result in?

In the example above the FIRST blank row of column S is now S6. Is that where you would want the result to go? And would the sum be S2:S5, or still all of S? Having the formula added in the middle would create a circular reference as the result would be included in the sum and would in theory increment indefinitely.

 

[rajm11]

You are right this is what I need


1. want the result to go in the FIRST blank row and not on the LAST row.
2. Sum of only as far as the cell with the result in?
2.1 . In the example above the FIRST blank row of column S is now S6. Is that where you would want the result to go? YES
2.2 And would the sum be S2:S5 Yes and go on till the end of the document for rows from s to AZ and then I will adjust formula for BA till BC

 

[Fishboy]

You are right this is what I need


1. want the result to go in the FIRST blank row and not on the LAST row.
2. Sum of only as far as the cell with the result in?
2.1 . In the example above the FIRST blank row of column S is now S6. Is that where you would want the result to go? YES
2.2 And would the sum be S2:S5 Yes and go on till the end of the document for rows from s to AZ and then I will adjust formula for BA till BC

Is this getting any closer to what you want?

It has been updated to look at S:AZ instead of S:BC
It finds the FIRST blank cell (whether it is in the middle OR on the end) and adds the formula

Sub ApplyFormulas()
Dim Cell As Range, cRange As Range, Rng As Range, LastRow As Long
    Set cRange = Range("S1:AZ1")
        For Each Cell In cRange
            LastRow = ActiveSheet.Cells(Rows.Count, Cell.Column).End(xlUp).Row
                With Range(Cells(2, Cell.Column), Cells(LastRow, Cell.Column))
                    Set Rng = .Find(What:="", _
                                After:=.Cells(1), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False)
                    If Not Rng Is Nothing Then
                        Rng.Formula = "=SUM(" & Cells(2, Cell.Column).Address & ":" & Rng.Offset(-1, 0).Address & ")/86400"
                    Else
                        Cells(LastRow + 1, Cell.Column).Formula = "=SUM(" & Cells(2, Cell.Column).Address & ":" & Cells(LastRow, Cell.Column).Address & ")/86400"
                    End If
                End With
        Next Cell
End Sub