Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|