VBA Sum

[Arbin]

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

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Gary Keramidas]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Arbin]

Thank you!