VBA Sum
 

I have an excel file where the number of rows can vary. I need to
summarize column B, C and D. The numbers starts at row 3 and there can
be blank cells in column B, C and/or D where values are missing. In
column A there is a text for each row (even those rows without values).
To further explain this:

Example column A3 to A98 has values, therefore column B, C and D should
be summarized with the sum written beneath each row for the columns 3 to
98. In the columns B, C and D there can be randomly empty cells (this
should not cause a problem with the sum function)

After the summaration of column B, C and D I want to take the
summarized value in column D and divide it with the summarized value in
column B, the result should be presented on the same row and in column E
(in the example the row would be E99)

I can get hold of the last row:
lastRow = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

But I don’t manage to summarize the values in the columns and present
the result beneath each row.

I hope that my explanation is clear enough

/Fredrik


--
Arbin
------------------------------------------------------------------------
Arbin's Profile: http://www.excelforum.com/member.php...o&userid=30542
View this thread: http://www.excelforum.com/showthread...hreadid=501953

VBA Sum
 

you can give this a try

Option Explicit
Sub test()
Dim lastrow As Long
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

Range("A" & lastrow + 1).Formula = "=sum(a3:a" & lastrow & ")"
Range("A" & lastrow + 1).AutoFill _
Range("a" & lastrow + 1 & ":d" & lastrow + 1)

End Sub

--


Gary


"Arbin" wrote in
message ...

I have an excel file where the number of rows can vary. I need to
summarize column B, C and D. The numbers starts at row 3 and there can
be blank cells in column B, C and/or D where values are missing. In
column A there is a text for each row (even those rows without values).
To further explain this:

Example column A3 to A98 has values, therefore column B, C and D should
be summarized with the sum written beneath each row for the columns 3 to
98. In the columns B, C and D there can be randomly empty cells (this
should not cause a problem with the sum function)

After the summaration of column B, C and D I want to take the
summarized value in column D and divide it with the summarized value in
column B, the result should be presented on the same row and in column E
(in the example the row would be E99)

I can get hold of the last row:
lastRow = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

But I don’t manage to summarize the values in the columns and present
the result beneath each row.

I hope that my explanation is clear enough

/Fredrik


--
Arbin
------------------------------------------------------------------------
Arbin's Profile:
http://www.excelforum.com/member.php...o&userid=30542
View this thread: http://www.excelforum.com/showthread...hreadid=501953

VBA Sum
 

iLastRow = Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
Cells(iLastRow + 1, "B").Formula = "=SUM(B3:B" & iLastRow & ")"
Cells(iLastRow + 1, "D").Formula = "=SUM(D3:D" & iLastRow & ")"
Cells(iLastRow + 1, "E").Formula = "=D" & iLastRow + 1 & "/B" & iLastRow
+ 1


--
HTH

RP
"Arbin" wrote in
message ...

I have an excel file where the number of rows can vary. I need to
summarize column B, C and D. The numbers starts at row 3 and there can
be blank cells in column B, C and/or D where values are missing. In
column A there is a text for each row (even those rows without values).
To further explain this:

Example column A3 to A98 has values, therefore column B, C and D should
be summarized with the sum written beneath each row for the columns 3 to
98. In the columns B, C and D there can be randomly empty cells (this
should not cause a problem with the sum function)

After the summaration of column B, C and D I want to take the
summarized value in column D and divide it with the summarized value in
column B, the result should be presented on the same row and in column E
(in the example the row would be E99)

I can get hold of the last row:
lastRow = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

But I don't manage to summarize the values in the columns and present
the result beneath each row.

I hope that my explanation is clear enough

/Fredrik


--
Arbin
------------------------------------------------------------------------
Arbin's Profile:
http://www.excelforum.com/member.php...o&userid=30542
View this thread: http://www.excelforum.com/showthread...hreadid=501953

VBA Sum
 

Thank you!


--
Arbin
------------------------------------------------------------------------
Arbin's Profile: http://www.excelforum.com/member.php...o&userid=30542
View this thread: http://www.excelforum.com/showthread...hreadid=501953