Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum different sized (employee) teams
Hi,
I have some VB code (an answer from another question) written by vezerid which I found very useful and have used in my sheet. On the extra row that is being created - is it possible to sum the cells above (of certain columns) please? I've used the VB below to put an extra row in after every change in team name within my organisation (column A). Some teams have 10 people in, while others have 100+ so putting this blank row automatically is very useful to separate. I then go through the sheet and manually sum the columns required (T, U and BK) for each team. I've tried to amend the code myself but I can't seem to get it to work properly, as some of the 'sums' are for the 10 rows above and then some are for 100. Is it possible to amend the code to include the sum for these 3 colums please? Thanks in advance, AW "vezerid" wrote: Sub InsertRows() StartRow = 2 'Change the 2 to the row actual data start DataColumn = 1 'Change the 1 to the column where your data is i = StartRow + 1 While Cells(i, DataColumn) < "" If Cells(i, DataColumn) < Cells(i - 1, DataColumn) Then Cells(i, DataColumn).EntireRow.Insert i = i + 1 End If i = i + 1 Wend End Sub |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum different sized (employee) teams
This will put sums on all but the last set of data. Sorry its not a complete
solution. Sub InsertRows() StartRow = 2 'Change the 2 to the row actual data start DataColumn = 1 'Change the 1 to the column where your data is i = StartRow + 1 x = StartRow While Cells(i, DataColumn) < "" If Cells(i, DataColumn) < Cells(i - 1, DataColumn) Then Cells(i, DataColumn).EntireRow.Insert Range("T" & i).Formula = "=sum(T" & x & ":T" & i - 1 & ")" Range("U" & i).Formula = "=sum(U" & x & ":U" & i - 1 & ")" Range("BK" & i).Formula = "=sum(BK" & x & ":BK" & i - 1 & ")" i = i + 1 x = i End If i = i + 1 Wend End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "ArcticWolf" wrote: Hi, I have some VB code (an answer from another question) written by vezerid which I found very useful and have used in my sheet. On the extra row that is being created - is it possible to sum the cells above (of certain columns) please? I've used the VB below to put an extra row in after every change in team name within my organisation (column A). Some teams have 10 people in, while others have 100+ so putting this blank row automatically is very useful to separate. I then go through the sheet and manually sum the columns required (T, U and BK) for each team. I've tried to amend the code myself but I can't seem to get it to work properly, as some of the 'sums' are for the 10 rows above and then some are for 100. Is it possible to amend the code to include the sum for these 3 colums please? Thanks in advance, AW "vezerid" wrote: Sub InsertRows() StartRow = 2 'Change the 2 to the row actual data start DataColumn = 1 'Change the 1 to the column where your data is i = StartRow + 1 While Cells(i, DataColumn) < "" If Cells(i, DataColumn) < Cells(i - 1, DataColumn) Then Cells(i, DataColumn).EntireRow.Insert i = i + 1 End If i = i + 1 Wend End Sub |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum different sized (employee) teams
Complete solution. Sorry I missed this earlier.
Sub InsertRows() StartRow = 2 'Change the 2 to the row actual data start DataColumn = 1 'Change the 1 to the column where your data is i = StartRow + 1 x = StartRow While Cells(i, DataColumn) < "" If Cells(i, DataColumn) < Cells(i - 1, DataColumn) Then Cells(i, DataColumn).EntireRow.Insert Range("T" & i).Formula = "=sum(T" & x & ":T" & i - 1 & ")" Range("U" & i).Formula = "=sum(U" & x & ":U" & i - 1 & ")" Range("BK" & i).Formula = "=sum(BK" & x & ":BK" & i - 1 & ")" i = i + 1 x = i End If i = i + 1 Wend Range("T" & i).Formula = "=sum(T" & x & ":T" & i - 1 & ")" Range("U" & i).Formula = "=sum(U" & x & ":U" & i - 1 & ")" Range("BK" & i).Formula = "=sum(BK" & x & ":BK" & i - 1 & ")" End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: This will put sums on all but the last set of data. Sorry its not a complete solution. Sub InsertRows() StartRow = 2 'Change the 2 to the row actual data start DataColumn = 1 'Change the 1 to the column where your data is i = StartRow + 1 x = StartRow While Cells(i, DataColumn) < "" If Cells(i, DataColumn) < Cells(i - 1, DataColumn) Then Cells(i, DataColumn).EntireRow.Insert Range("T" & i).Formula = "=sum(T" & x & ":T" & i - 1 & ")" Range("U" & i).Formula = "=sum(U" & x & ":U" & i - 1 & ")" Range("BK" & i).Formula = "=sum(BK" & x & ":BK" & i - 1 & ")" i = i + 1 x = i End If i = i + 1 Wend End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "ArcticWolf" wrote: Hi, I have some VB code (an answer from another question) written by vezerid which I found very useful and have used in my sheet. On the extra row that is being created - is it possible to sum the cells above (of certain columns) please? I've used the VB below to put an extra row in after every change in team name within my organisation (column A). Some teams have 10 people in, while others have 100+ so putting this blank row automatically is very useful to separate. I then go through the sheet and manually sum the columns required (T, U and BK) for each team. I've tried to amend the code myself but I can't seem to get it to work properly, as some of the 'sums' are for the 10 rows above and then some are for 100. Is it possible to amend the code to include the sum for these 3 colums please? Thanks in advance, AW "vezerid" wrote: Sub InsertRows() StartRow = 2 'Change the 2 to the row actual data start DataColumn = 1 'Change the 1 to the column where your data is i = StartRow + 1 While Cells(i, DataColumn) < "" If Cells(i, DataColumn) < Cells(i - 1, DataColumn) Then Cells(i, DataColumn).EntireRow.Insert i = i + 1 End If i = i + 1 Wend End Sub |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum different sized (employee) teams
Works perfect - thanks for you help Luke :)
ATB, AW "Luke M" wrote: Complete solution. Sorry I missed this earlier. Sub InsertRows() StartRow = 2 'Change the 2 to the row actual data start DataColumn = 1 'Change the 1 to the column where your data is i = StartRow + 1 x = StartRow While Cells(i, DataColumn) < "" If Cells(i, DataColumn) < Cells(i - 1, DataColumn) Then Cells(i, DataColumn).EntireRow.Insert Range("T" & i).Formula = "=sum(T" & x & ":T" & i - 1 & ")" Range("U" & i).Formula = "=sum(U" & x & ":U" & i - 1 & ")" Range("BK" & i).Formula = "=sum(BK" & x & ":BK" & i - 1 & ")" i = i + 1 x = i End If i = i + 1 Wend Range("T" & i).Formula = "=sum(T" & x & ":T" & i - 1 & ")" Range("U" & i).Formula = "=sum(U" & x & ":U" & i - 1 & ")" Range("BK" & i).Formula = "=sum(BK" & x & ":BK" & i - 1 & ")" End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: This will put sums on all but the last set of data. Sorry its not a complete solution. Sub InsertRows() StartRow = 2 'Change the 2 to the row actual data start DataColumn = 1 'Change the 1 to the column where your data is i = StartRow + 1 x = StartRow While Cells(i, DataColumn) < "" If Cells(i, DataColumn) < Cells(i - 1, DataColumn) Then Cells(i, DataColumn).EntireRow.Insert Range("T" & i).Formula = "=sum(T" & x & ":T" & i - 1 & ")" Range("U" & i).Formula = "=sum(U" & x & ":U" & i - 1 & ")" Range("BK" & i).Formula = "=sum(BK" & x & ":BK" & i - 1 & ")" i = i + 1 x = i End If i = i + 1 Wend End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "ArcticWolf" wrote: Hi, I have some VB code (an answer from another question) written by vezerid which I found very useful and have used in my sheet. On the extra row that is being created - is it possible to sum the cells above (of certain columns) please? I've used the VB below to put an extra row in after every change in team name within my organisation (column A). Some teams have 10 people in, while others have 100+ so putting this blank row automatically is very useful to separate. I then go through the sheet and manually sum the columns required (T, U and BK) for each team. I've tried to amend the code myself but I can't seem to get it to work properly, as some of the 'sums' are for the 10 rows above and then some are for 100. Is it possible to amend the code to include the sum for these 3 colums please? Thanks in advance, AW "vezerid" wrote: Sub InsertRows() StartRow = 2 'Change the 2 to the row actual data start DataColumn = 1 'Change the 1 to the column where your data is i = StartRow + 1 While Cells(i, DataColumn) < "" If Cells(i, DataColumn) < Cells(i - 1, DataColumn) Then Cells(i, DataColumn).EntireRow.Insert i = i + 1 End If i = i + 1 Wend End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Bowling: Handicap between Teams | Excel Worksheet Functions | |||
Does any one have a Golf Tournament spreadsheet for teams? | Excel Discussion (Misc queries) | |||
Calculate employee hours for employee evaluation? | Excel Worksheet Functions | |||
Fromula to take employee # out of employee name field | Excel Discussion (Misc queries) | |||
transfering numbers into teams | Excel Worksheet Functions |