Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Formula Help
I am trying to create a spreadsheet for my work. I'll try to explain what
I'm trying to accomplish so you'll have a better understanding of how I'd like the spreadsheet setup. I work for an investment company and we have several investment managers. Each investment manager has their own seprate book of clients. Each separate client account has 19 major variables that I listed below. I will be exporting each investment manager's book of business into excel. Eventually I'll have 60+ tabs so I will need a macro to save time. For this project,I'm trying to find the market value of each client relationship. The market value is simply the dollar amount of each account. The relationship code is a link of seperate accounts that can be viewed togeher as one. There can be as many as 50 accounts within a relationship or as few as 1 account. Here is an example what I'm trying to accomplish: John Smith has an account ($1,000,000), his wife, Joan, has an account ($500,000), and his son, Joe, has an account ($500,000). This family would all have the same relationship code (100). I want a spreadsheet that sums the market value of the three accounts within relationship code 100 for a total of $2,000,000. Jane Doe has an unrelated account. Here relationship code is 200, and her account has a market value of $1,000,000. I want to see that relationship code 100 has a market value of $2,000,000 and relationship code 200 has a market value of 200. These variables will be exported in the same order each time and will always be in cells A1:S1. These variables a Account Name (this will always be cell A1) Account # Rel. Code Type Revocability Alpha Sequence Market Value Prior Yr Revenue YTD Revenue Investment Responsibility Administrator Admin # Investment Officer Inv Officer # Real Estate Officer R.E. Officer # Tax Officer Tax Officer # Client Advisor (This will always be the cell S1) I would like to have all of the data sorted and grouped by the category "Rel. Code" which is always in column C. Once the data is organized I would then like a formula that sums the market value (column G) of all of the accounts that have the same "Rel. Code". When the market value is summed, I would like that value to be in bold. I would then like a blank row inserted below. In the Smith example above, the data will be exported into excel as follows: John Smith (rel code in C2) (market value in G2) Joan Smith (rel code in C3) (market value in G3) Joe Smith (rel code in C4) (market value in G4) Jane Doe (rel code in C5) (market value in G5) After the macro is applied, I would like the shreadsheet to be setup like this: John Smith (rel code in C2) (market value in G2) Joan Smith (rel code in C3) (market value in G3) Joe Smith (rel code in C4) (market value in G4) G5 will have the sum of the market values of G2,G3, and G4 in bold; all other cells in row 5 will be blank. All cells in row 6 will be blank. Jane Doe (rel code in C7) (market value in G7) G8 will have the sum of the market value of G7 in bold; all other cells in row 8 will be blank. All cells in row 9 will be blank. Once this is done, please autofit the columns in the spreadsheet. Thank you all of your help, I hope I was clear in describing this spreadsheet. Please let me know if you have any other questions. Curt J. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Formula Help
"Please let me know if you have any other questions."
What is the budget for this project? -- Jim Cone Portland, Oregon USA "Curt J" wrote in message I am trying to create a spreadsheet for my work. I'll try to explain what I'm trying to accomplish so you'll have a better understanding of how I'd like the spreadsheet setup. I work for an investment company and we have several investment managers. Each investment manager has their own seprate book of clients. Each separate client account has 19 major variables that I listed below. I will be exporting each investment manager's book of business into excel. Eventually I'll have 60+ tabs so I will need a macro to save time. For this project,I'm trying to find the market value of each client relationship. The market value is simply the dollar amount of each account. The relationship code is a link of seperate accounts that can be viewed togeher as one. There can be as many as 50 accounts within a relationship or as few as 1 account. Here is an example what I'm trying to accomplish: John Smith has an account ($1,000,000), his wife, Joan, has an account ($500,000), and his son, Joe, has an account ($500,000). This family would all have the same relationship code (100). I want a spreadsheet that sums the market value of the three accounts within relationship code 100 for a total of $2,000,000. Jane Doe has an unrelated account. Here relationship code is 200, and her account has a market value of $1,000,000. I want to see that relationship code 100 has a market value of $2,000,000 and relationship code 200 has a market value of 200. These variables will be exported in the same order each time and will always be in cells A1:S1. These variables a Account Name (this will always be cell A1) Account # Rel. Code Type Revocability Alpha Sequence Market Value Prior Yr Revenue YTD Revenue Investment Responsibility Administrator Admin # Investment Officer Inv Officer # Real Estate Officer R.E. Officer # Tax Officer Tax Officer # Client Advisor (This will always be the cell S1) I would like to have all of the data sorted and grouped by the category "Rel. Code" which is always in column C. Once the data is organized I would then like a formula that sums the market value (column G) of all of the accounts that have the same "Rel. Code". When the market value is summed, I would like that value to be in bold. I would then like a blank row inserted below. In the Smith example above, the data will be exported into excel as follows: John Smith (rel code in C2) (market value in G2) Joan Smith (rel code in C3) (market value in G3) Joe Smith (rel code in C4) (market value in G4) Jane Doe (rel code in C5) (market value in G5) After the macro is applied, I would like the shreadsheet to be setup like this: John Smith (rel code in C2) (market value in G2) Joan Smith (rel code in C3) (market value in G3) Joe Smith (rel code in C4) (market value in G4) G5 will have the sum of the market values of G2,G3, and G4 in bold; all other cells in row 5 will be blank. All cells in row 6 will be blank. Jane Doe (rel code in C7) (market value in G7) G8 will have the sum of the market value of G7 in bold; all other cells in row 8 will be blank. All cells in row 9 will be blank. Once this is done, please autofit the columns in the spreadsheet. Thank you all of your help, I hope I was clear in describing this spreadsheet. Please let me know if you have any other questions. Curt J. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Formula Help
Hi Jim,
Unfortunatly, there isn't a budget. I had a similar request several months ago and someone answered my question free of charge. I'm hoping this will happen again this time. If no one posts an answer, I'll end up having to do it manually. "Jim Cone" wrote: "Please let me know if you have any other questions." What is the budget for this project? -- Jim Cone Portland, Oregon USA "Curt J" wrote in message I am trying to create a spreadsheet for my work. I'll try to explain what I'm trying to accomplish so you'll have a better understanding of how I'd like the spreadsheet setup. I work for an investment company and we have several investment managers. Each investment manager has their own seprate book of clients. Each separate client account has 19 major variables that I listed below. I will be exporting each investment manager's book of business into excel. Eventually I'll have 60+ tabs so I will need a macro to save time. For this project,I'm trying to find the market value of each client relationship. The market value is simply the dollar amount of each account. The relationship code is a link of seperate accounts that can be viewed togeher as one. There can be as many as 50 accounts within a relationship or as few as 1 account. Here is an example what I'm trying to accomplish: John Smith has an account ($1,000,000), his wife, Joan, has an account ($500,000), and his son, Joe, has an account ($500,000). This family would all have the same relationship code (100). I want a spreadsheet that sums the market value of the three accounts within relationship code 100 for a total of $2,000,000. Jane Doe has an unrelated account. Here relationship code is 200, and her account has a market value of $1,000,000. I want to see that relationship code 100 has a market value of $2,000,000 and relationship code 200 has a market value of 200. These variables will be exported in the same order each time and will always be in cells A1:S1. These variables a Account Name (this will always be cell A1) Account # Rel. Code Type Revocability Alpha Sequence Market Value Prior Yr Revenue YTD Revenue Investment Responsibility Administrator Admin # Investment Officer Inv Officer # Real Estate Officer R.E. Officer # Tax Officer Tax Officer # Client Advisor (This will always be the cell S1) I would like to have all of the data sorted and grouped by the category "Rel. Code" which is always in column C. Once the data is organized I would then like a formula that sums the market value (column G) of all of the accounts that have the same "Rel. Code". When the market value is summed, I would like that value to be in bold. I would then like a blank row inserted below. In the Smith example above, the data will be exported into excel as follows: John Smith (rel code in C2) (market value in G2) Joan Smith (rel code in C3) (market value in G3) Joe Smith (rel code in C4) (market value in G4) Jane Doe (rel code in C5) (market value in G5) After the macro is applied, I would like the shreadsheet to be setup like this: John Smith (rel code in C2) (market value in G2) Joan Smith (rel code in C3) (market value in G3) Joe Smith (rel code in C4) (market value in G4) G5 will have the sum of the market values of G2,G3, and G4 in bold; all other cells in row 5 will be blank. All cells in row 6 will be blank. Jane Doe (rel code in C7) (market value in G7) G8 will have the sum of the market value of G7 in bold; all other cells in row 8 will be blank. All cells in row 9 will be blank. Once this is done, please autofit the columns in the spreadsheet. Thank you all of your help, I hope I was clear in describing this spreadsheet. Please let me know if you have any other questions. Curt J. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Formula Help
Your explaination is much more complicated then the code. This is pretty
simple Sub SumAccounts() For Each Sht In Sheets With Sht RowCount = 1 FirstRow = RowCount Do While .Range("B" & RowCount) < "" 'check if account number or Rel Code does not match on adjecent rows If .Range("B" & RowCount) < .Range("B" & (RowCount + 1)) Or _ .Range("C" & RowCount) < .Range("C" & (RowCount + 1)) Then 'Insert Two Blank rows .Rows(RowCount + 1).Insert .Rows(RowCount + 1).Insert 'Use worksheet formula to sum column g for Rel code .Range("G" & (RowCount + 1)).Formula = _ "=Sum(G" & FirstRow & ":G" & RowCount & ")" .Range("G" & (RowCount + 1)).Bold = True RowCount = RowCount + 3 FirstRow = RowCount Else RowCount = RowCount + 1 End If Loop End With Next Sht End Sub "Curt J" wrote: I am trying to create a spreadsheet for my work. I'll try to explain what I'm trying to accomplish so you'll have a better understanding of how I'd like the spreadsheet setup. I work for an investment company and we have several investment managers. Each investment manager has their own seprate book of clients. Each separate client account has 19 major variables that I listed below. I will be exporting each investment manager's book of business into excel. Eventually I'll have 60+ tabs so I will need a macro to save time. For this project,I'm trying to find the market value of each client relationship. The market value is simply the dollar amount of each account. The relationship code is a link of seperate accounts that can be viewed togeher as one. There can be as many as 50 accounts within a relationship or as few as 1 account. Here is an example what I'm trying to accomplish: John Smith has an account ($1,000,000), his wife, Joan, has an account ($500,000), and his son, Joe, has an account ($500,000). This family would all have the same relationship code (100). I want a spreadsheet that sums the market value of the three accounts within relationship code 100 for a total of $2,000,000. Jane Doe has an unrelated account. Here relationship code is 200, and her account has a market value of $1,000,000. I want to see that relationship code 100 has a market value of $2,000,000 and relationship code 200 has a market value of 200. These variables will be exported in the same order each time and will always be in cells A1:S1. These variables a Account Name (this will always be cell A1) Account # Rel. Code Type Revocability Alpha Sequence Market Value Prior Yr Revenue YTD Revenue Investment Responsibility Administrator Admin # Investment Officer Inv Officer # Real Estate Officer R.E. Officer # Tax Officer Tax Officer # Client Advisor (This will always be the cell S1) I would like to have all of the data sorted and grouped by the category "Rel. Code" which is always in column C. Once the data is organized I would then like a formula that sums the market value (column G) of all of the accounts that have the same "Rel. Code". When the market value is summed, I would like that value to be in bold. I would then like a blank row inserted below. In the Smith example above, the data will be exported into excel as follows: John Smith (rel code in C2) (market value in G2) Joan Smith (rel code in C3) (market value in G3) Joe Smith (rel code in C4) (market value in G4) Jane Doe (rel code in C5) (market value in G5) After the macro is applied, I would like the shreadsheet to be setup like this: John Smith (rel code in C2) (market value in G2) Joan Smith (rel code in C3) (market value in G3) Joe Smith (rel code in C4) (market value in G4) G5 will have the sum of the market values of G2,G3, and G4 in bold; all other cells in row 5 will be blank. All cells in row 6 will be blank. Jane Doe (rel code in C7) (market value in G7) G8 will have the sum of the market value of G7 in bold; all other cells in row 8 will be blank. All cells in row 9 will be blank. Once this is done, please autofit the columns in the spreadsheet. Thank you all of your help, I hope I was clear in describing this spreadsheet. Please let me know if you have any other questions. Curt J. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Formula Help
I forgot the autofit
Sub SumAccounts() For Each Sht In Sheets With Sht RowCount = 1 FirstRow = RowCount Do While .Range("B" & RowCount) < "" 'check if account number of Rel Code does not match If .Range("B" & RowCount) < .Range("B" & (RowCount + 1)) Or _ .Range("C" & RowCount) < .Range("C" & (RowCount + 1)) Then 'Insert Two Blank rows .Rows(RowCount + 1).Insert .Rows(RowCount + 1).Insert 'Use worksheet formula to sum column g for Rel code .Range("G" & (RowCount + 1)).Formula = _ "=Sum(G" & FirstRow & ":G" & RowCount & ")" .Range("G" & (RowCount + 1)).Bold = True RowCount = RowCount + 3 FirstRow = RowCount Else RowCount = RowCount + 1 End If Loop End With .Columns("A:S").Columns.AutoFit Next Sht End Sub "Curt J" wrote: I am trying to create a spreadsheet for my work. I'll try to explain what I'm trying to accomplish so you'll have a better understanding of how I'd like the spreadsheet setup. I work for an investment company and we have several investment managers. Each investment manager has their own seprate book of clients. Each separate client account has 19 major variables that I listed below. I will be exporting each investment manager's book of business into excel. Eventually I'll have 60+ tabs so I will need a macro to save time. For this project,I'm trying to find the market value of each client relationship. The market value is simply the dollar amount of each account. The relationship code is a link of seperate accounts that can be viewed togeher as one. There can be as many as 50 accounts within a relationship or as few as 1 account. Here is an example what I'm trying to accomplish: John Smith has an account ($1,000,000), his wife, Joan, has an account ($500,000), and his son, Joe, has an account ($500,000). This family would all have the same relationship code (100). I want a spreadsheet that sums the market value of the three accounts within relationship code 100 for a total of $2,000,000. Jane Doe has an unrelated account. Here relationship code is 200, and her account has a market value of $1,000,000. I want to see that relationship code 100 has a market value of $2,000,000 and relationship code 200 has a market value of 200. These variables will be exported in the same order each time and will always be in cells A1:S1. These variables a Account Name (this will always be cell A1) Account # Rel. Code Type Revocability Alpha Sequence Market Value Prior Yr Revenue YTD Revenue Investment Responsibility Administrator Admin # Investment Officer Inv Officer # Real Estate Officer R.E. Officer # Tax Officer Tax Officer # Client Advisor (This will always be the cell S1) I would like to have all of the data sorted and grouped by the category "Rel. Code" which is always in column C. Once the data is organized I would then like a formula that sums the market value (column G) of all of the accounts that have the same "Rel. Code". When the market value is summed, I would like that value to be in bold. I would then like a blank row inserted below. In the Smith example above, the data will be exported into excel as follows: John Smith (rel code in C2) (market value in G2) Joan Smith (rel code in C3) (market value in G3) Joe Smith (rel code in C4) (market value in G4) Jane Doe (rel code in C5) (market value in G5) After the macro is applied, I would like the shreadsheet to be setup like this: John Smith (rel code in C2) (market value in G2) Joan Smith (rel code in C3) (market value in G3) Joe Smith (rel code in C4) (market value in G4) G5 will have the sum of the market values of G2,G3, and G4 in bold; all other cells in row 5 will be blank. All cells in row 6 will be blank. Jane Doe (rel code in C7) (market value in G7) G8 will have the sum of the market value of G7 in bold; all other cells in row 8 will be blank. All cells in row 9 will be blank. Once this is done, please autofit the columns in the spreadsheet. Thank you all of your help, I hope I was clear in describing this spreadsheet. Please let me know if you have any other questions. Curt J. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Formula Help
Hi Joel,
Thank you for looking into my problem. Unfortunatly, I haven't been able to get this to work yet. I am receiving the message "Run time Error '438': Object doesn't support this property or method." In case this helps you, I was given the following code below in the past. It ranks the rel codes by market value in descending order, and deletes some columns that were previously found to be useless. I tested this code and 8 different sheets. It worked for 7 of them, but on one of them it gave me the error message 13. Type mismatch. The code is: Sub fixworksheet() 'Delete unused rows ColCount = 1 Do While Cells(1, ColCount) < "" Heading = Cells(1, ColCount) Select Case Heading Case "Alpha Sequence", _ "Administrator", _ "Admin #", _ "Investment Officer", _ "Inv Officer #", _ "Real Estate Officer", _ "R.E. Officer #", _ "Tax Officer", _ "Tax Officer #" Columns(ColCount).Delete Case Else ColCount = ColCount + 1 End Select Loop 'Sort data by Rel. code Column LastRow = Cells(Rows.Count, "A").End(xlUp).Row LastCol = Cells(1, Columns.Count).End(xlToLeft).Column Set SortRange = Range("A2", Cells(LastRow, LastCol)) SortRange.Sort _ Key1:=Range("C2"), _ Order1:=xlAscending, _ Header:=xlGuess, _ MatchCase:=False, _ Orientation:=xlTopToBottom 'Find Market Value column Set Market = Rows(1).Find(what:="Market Value", LookIn:=xlValues) MarketCol = Market.Column 'Insert totals 'Plase total in first empty column where each group starts firstcol = LastCol + 1 'Place total in 2nd empty column where each group ends totalcol = LastCol + 2 RowCount = 2 FirstRow = RowCount Do While Cells(RowCount, "A") < "" If Cells(RowCount, "C") < _ Cells(RowCount + 1, "C") Then Rows(RowCount + 1).Insert Cells(RowCount + 1, MarketCol).FormulaR1C1 = _ "=Sum(R" & FirstRow & "C" & MarketCol & ":R" & _ RowCount & "C" & MarketCol & ")" Cells(RowCount + 1, MarketCol).Font.Bold = True Cells(FirstRow, firstcol) = _ Cells(RowCount + 1, MarketCol).Value Cells(RowCount + 1, totalcol) = _ Cells(RowCount + 1, MarketCol).Value RowCount = RowCount + 2 FirstRow = RowCount Else RowCount = RowCount + 1 End If Loop 'sort Groups LastRow = Cells(Rows.Count, MarketCol).End(xlUp).Row RowCount = 2 Do While RowCount <= LastRow If Cells(RowCount, firstcol) < "" Then TotalA = Cells(RowCount, firstcol) 'get beginning of next group CompareRow = RowCount + 1 Do While (CompareRow <= LastRow) Do While _ (Cells(CompareRow, firstcol) = "") And _ (CompareRow <= LastRow) CompareRow = CompareRow + 1 Loop If CompareRow <= LastRow Then TotalB = Cells(CompareRow, firstcol) 'if TotalB totalA than insert '2nd group in front of first group If TotalB TotalA Then EndRow = CompareRow Do While Cells(EndRow, totalcol) = "" EndRow = EndRow + 1 Loop Rows(CompareRow & ":" & EndRow).Cut Rows(RowCount).Insert Shift:=xlDown TotalA = TotalB CompareRow = RowCount + 1 Else CompareRow = CompareRow + 1 End If End If Loop End If RowCount = RowCount + 1 Loop 'add blank rows RowCount = LastRow Do While RowCount = 1 If Cells(RowCount, firstcol) < "" Then If RowCount < 2 Then Rows(RowCount).Insert End If End If RowCount = RowCount - 1 Loop 'delete auxilary columns added for running this maacro Columns(totalcol).Delete Columns(firstcol).Delete End Sub Thanks Again, Curt J "Joel" wrote: I forgot the autofit Sub SumAccounts() For Each Sht In Sheets With Sht RowCount = 1 FirstRow = RowCount Do While .Range("B" & RowCount) < "" 'check if account number of Rel Code does not match If .Range("B" & RowCount) < .Range("B" & (RowCount + 1)) Or _ .Range("C" & RowCount) < .Range("C" & (RowCount + 1)) Then 'Insert Two Blank rows .Rows(RowCount + 1).Insert .Rows(RowCount + 1).Insert 'Use worksheet formula to sum column g for Rel code .Range("G" & (RowCount + 1)).Formula = _ "=Sum(G" & FirstRow & ":G" & RowCount & ")" .Range("G" & (RowCount + 1)).Bold = True RowCount = RowCount + 3 FirstRow = RowCount Else RowCount = RowCount + 1 End If Loop End With .Columns("A:S").Columns.AutoFit Next Sht End Sub "Curt J" wrote: I am trying to create a spreadsheet for my work. I'll try to explain what I'm trying to accomplish so you'll have a better understanding of how I'd like the spreadsheet setup. I work for an investment company and we have several investment managers. Each investment manager has their own seprate book of clients. Each separate client account has 19 major variables that I listed below. I will be exporting each investment manager's book of business into excel. Eventually I'll have 60+ tabs so I will need a macro to save time. For this project,I'm trying to find the market value of each client relationship. The market value is simply the dollar amount of each account. The relationship code is a link of seperate accounts that can be viewed togeher as one. There can be as many as 50 accounts within a relationship or as few as 1 account. Here is an example what I'm trying to accomplish: John Smith has an account ($1,000,000), his wife, Joan, has an account ($500,000), and his son, Joe, has an account ($500,000). This family would all have the same relationship code (100). I want a spreadsheet that sums the market value of the three accounts within relationship code 100 for a total of $2,000,000. Jane Doe has an unrelated account. Here relationship code is 200, and her account has a market value of $1,000,000. I want to see that relationship code 100 has a market value of $2,000,000 and relationship code 200 has a market value of 200. These variables will be exported in the same order each time and will always be in cells A1:S1. These variables a Account Name (this will always be cell A1) Account # Rel. Code Type Revocability Alpha Sequence Market Value Prior Yr Revenue YTD Revenue Investment Responsibility Administrator Admin # Investment Officer Inv Officer # Real Estate Officer R.E. Officer # Tax Officer Tax Officer # Client Advisor (This will always be the cell S1) I would like to have all of the data sorted and grouped by the category "Rel. Code" which is always in column C. Once the data is organized I would then like a formula that sums the market value (column G) of all of the accounts that have the same "Rel. Code". When the market value is summed, I would like that value to be in bold. I would then like a blank row inserted below. In the Smith example above, the data will be exported into excel as follows: John Smith (rel code in C2) (market value in G2) Joan Smith (rel code in C3) (market value in G3) Joe Smith (rel code in C4) (market value in G4) Jane Doe (rel code in C5) (market value in G5) After the macro is applied, I would like the shreadsheet to be setup like this: John Smith (rel code in C2) (market value in G2) Joan Smith (rel code in C3) (market value in G3) Joe Smith (rel code in C4) (market value in G4) G5 will have the sum of the market values of G2,G3, and G4 in bold; all other cells in row 5 will be blank. All cells in row 6 will be blank. Jane Doe (rel code in C7) (market value in G7) G8 will have the sum of the market value of G7 in bold; all other cells in row 8 will be blank. All cells in row 9 will be blank. Once this is done, please autofit the columns in the spreadsheet. Thank you all of your help, I hope I was clear in describing this spreadsheet. Please let me know if you have any other questions. Curt J. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Formula Help
I don't know what is failing so it is hard to give an answer. One thing I
notice was the FIND didn't check if a value was returned. Try this change it may be causing the error. 'Find Market Value column Set Market = Rows(1).Find(what:="Market Value", LookIn:=xlValues) if Market is nothing then msgbox("Error - could not find Market Value") stop else MarketCol = Market.Column end if "Curt J" wrote: Hi Joel, Thank you for looking into my problem. Unfortunatly, I haven't been able to get this to work yet. I am receiving the message "Run time Error '438': Object doesn't support this property or method." In case this helps you, I was given the following code below in the past. It ranks the rel codes by market value in descending order, and deletes some columns that were previously found to be useless. I tested this code and 8 different sheets. It worked for 7 of them, but on one of them it gave me the error message 13. Type mismatch. The code is: Sub fixworksheet() 'Delete unused rows ColCount = 1 Do While Cells(1, ColCount) < "" Heading = Cells(1, ColCount) Select Case Heading Case "Alpha Sequence", _ "Administrator", _ "Admin #", _ "Investment Officer", _ "Inv Officer #", _ "Real Estate Officer", _ "R.E. Officer #", _ "Tax Officer", _ "Tax Officer #" Columns(ColCount).Delete Case Else ColCount = ColCount + 1 End Select Loop 'Sort data by Rel. code Column LastRow = Cells(Rows.Count, "A").End(xlUp).Row LastCol = Cells(1, Columns.Count).End(xlToLeft).Column Set SortRange = Range("A2", Cells(LastRow, LastCol)) SortRange.Sort _ Key1:=Range("C2"), _ Order1:=xlAscending, _ Header:=xlGuess, _ MatchCase:=False, _ Orientation:=xlTopToBottom 'Find Market Value column Set Market = Rows(1).Find(what:="Market Value", LookIn:=xlValues) MarketCol = Market.Column 'Insert totals 'Plase total in first empty column where each group starts firstcol = LastCol + 1 'Place total in 2nd empty column where each group ends totalcol = LastCol + 2 RowCount = 2 FirstRow = RowCount Do While Cells(RowCount, "A") < "" If Cells(RowCount, "C") < _ Cells(RowCount + 1, "C") Then Rows(RowCount + 1).Insert Cells(RowCount + 1, MarketCol).FormulaR1C1 = _ "=Sum(R" & FirstRow & "C" & MarketCol & ":R" & _ RowCount & "C" & MarketCol & ")" Cells(RowCount + 1, MarketCol).Font.Bold = True Cells(FirstRow, firstcol) = _ Cells(RowCount + 1, MarketCol).Value Cells(RowCount + 1, totalcol) = _ Cells(RowCount + 1, MarketCol).Value RowCount = RowCount + 2 FirstRow = RowCount Else RowCount = RowCount + 1 End If Loop 'sort Groups LastRow = Cells(Rows.Count, MarketCol).End(xlUp).Row RowCount = 2 Do While RowCount <= LastRow If Cells(RowCount, firstcol) < "" Then TotalA = Cells(RowCount, firstcol) 'get beginning of next group CompareRow = RowCount + 1 Do While (CompareRow <= LastRow) Do While _ (Cells(CompareRow, firstcol) = "") And _ (CompareRow <= LastRow) CompareRow = CompareRow + 1 Loop If CompareRow <= LastRow Then TotalB = Cells(CompareRow, firstcol) 'if TotalB totalA than insert '2nd group in front of first group If TotalB TotalA Then EndRow = CompareRow Do While Cells(EndRow, totalcol) = "" EndRow = EndRow + 1 Loop Rows(CompareRow & ":" & EndRow).Cut Rows(RowCount).Insert Shift:=xlDown TotalA = TotalB CompareRow = RowCount + 1 Else CompareRow = CompareRow + 1 End If End If Loop End If RowCount = RowCount + 1 Loop 'add blank rows RowCount = LastRow Do While RowCount = 1 If Cells(RowCount, firstcol) < "" Then If RowCount < 2 Then Rows(RowCount).Insert End If End If RowCount = RowCount - 1 Loop 'delete auxilary columns added for running this maacro Columns(totalcol).Delete Columns(firstcol).Delete End Sub Thanks Again, Curt J "Joel" wrote: I forgot the autofit Sub SumAccounts() For Each Sht In Sheets With Sht RowCount = 1 FirstRow = RowCount Do While .Range("B" & RowCount) < "" 'check if account number of Rel Code does not match If .Range("B" & RowCount) < .Range("B" & (RowCount + 1)) Or _ .Range("C" & RowCount) < .Range("C" & (RowCount + 1)) Then 'Insert Two Blank rows .Rows(RowCount + 1).Insert .Rows(RowCount + 1).Insert 'Use worksheet formula to sum column g for Rel code .Range("G" & (RowCount + 1)).Formula = _ "=Sum(G" & FirstRow & ":G" & RowCount & ")" .Range("G" & (RowCount + 1)).Bold = True RowCount = RowCount + 3 FirstRow = RowCount Else RowCount = RowCount + 1 End If Loop End With .Columns("A:S").Columns.AutoFit Next Sht End Sub "Curt J" wrote: I am trying to create a spreadsheet for my work. I'll try to explain what I'm trying to accomplish so you'll have a better understanding of how I'd like the spreadsheet setup. I work for an investment company and we have several investment managers. Each investment manager has their own seprate book of clients. Each separate client account has 19 major variables that I listed below. I will be exporting each investment manager's book of business into excel. Eventually I'll have 60+ tabs so I will need a macro to save time. For this project,I'm trying to find the market value of each client relationship. The market value is simply the dollar amount of each account. The relationship code is a link of seperate accounts that can be viewed togeher as one. There can be as many as 50 accounts within a relationship or as few as 1 account. Here is an example what I'm trying to accomplish: John Smith has an account ($1,000,000), his wife, Joan, has an account ($500,000), and his son, Joe, has an account ($500,000). This family would all have the same relationship code (100). I want a spreadsheet that sums the market value of the three accounts within relationship code 100 for a total of $2,000,000. Jane Doe has an unrelated account. Here relationship code is 200, and her account has a market value of $1,000,000. I want to see that relationship code 100 has a market value of $2,000,000 and relationship code 200 has a market value of 200. These variables will be exported in the same order each time and will always be in cells A1:S1. These variables a Account Name (this will always be cell A1) Account # Rel. Code Type Revocability Alpha Sequence Market Value Prior Yr Revenue YTD Revenue Investment Responsibility Administrator Admin # Investment Officer Inv Officer # Real Estate Officer R.E. Officer # Tax Officer Tax Officer # Client Advisor (This will always be the cell S1) I would like to have all of the data sorted and grouped by the category "Rel. Code" which is always in column C. Once the data is organized I would then like a formula that sums the market value (column G) of all of the accounts that have the same "Rel. Code". When the market value is summed, I would like that value to be in bold. I would then like a blank row inserted below. In the Smith example above, the data will be exported into excel as follows: John Smith (rel code in C2) (market value in G2) Joan Smith (rel code in C3) (market value in G3) Joe Smith (rel code in C4) (market value in G4) Jane Doe (rel code in C5) (market value in G5) After the macro is applied, I would like the shreadsheet to be setup like this: John Smith (rel code in C2) (market value in G2) Joan Smith (rel code in C3) (market value in G3) Joe Smith (rel code in C4) (market value in G4) G5 will have the sum of the market values of G2,G3, and G4 in bold; all other cells in row 5 will be blank. All cells in row 6 will be blank. Jane Doe (rel code in C7) (market value in G7) G8 will have the sum of the market value of G7 in bold; all other cells in row 8 will be blank. All cells in row 9 will be blank. Once this is done, please autofit the columns in the spreadsheet. Thank you all of your help, I hope I was clear in describing this spreadsheet. Please let me know if you have any other questions. Curt J. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Formula Help
A suggested improvement. When sorting the groups to get the last row of the
group use end(xldown). You already put a space between the groups and the last row will be the last row of data before the empty row 'get beginning of next group EndGroup = Range("C" & Rowcount).End(xldown).Row 'Start of new Group StartofGroup = EndGroup + 2 "Curt J" wrote: Hi Joel, Thank you for looking into my problem. Unfortunatly, I haven't been able to get this to work yet. I am receiving the message "Run time Error '438': Object doesn't support this property or method." In case this helps you, I was given the following code below in the past. It ranks the rel codes by market value in descending order, and deletes some columns that were previously found to be useless. I tested this code and 8 different sheets. It worked for 7 of them, but on one of them it gave me the error message 13. Type mismatch. The code is: Sub fixworksheet() 'Delete unused rows ColCount = 1 Do While Cells(1, ColCount) < "" Heading = Cells(1, ColCount) Select Case Heading Case "Alpha Sequence", _ "Administrator", _ "Admin #", _ "Investment Officer", _ "Inv Officer #", _ "Real Estate Officer", _ "R.E. Officer #", _ "Tax Officer", _ "Tax Officer #" Columns(ColCount).Delete Case Else ColCount = ColCount + 1 End Select Loop 'Sort data by Rel. code Column LastRow = Cells(Rows.Count, "A").End(xlUp).Row LastCol = Cells(1, Columns.Count).End(xlToLeft).Column Set SortRange = Range("A2", Cells(LastRow, LastCol)) SortRange.Sort _ Key1:=Range("C2"), _ Order1:=xlAscending, _ Header:=xlGuess, _ MatchCase:=False, _ Orientation:=xlTopToBottom 'Find Market Value column Set Market = Rows(1).Find(what:="Market Value", LookIn:=xlValues) MarketCol = Market.Column 'Insert totals 'Plase total in first empty column where each group starts firstcol = LastCol + 1 'Place total in 2nd empty column where each group ends totalcol = LastCol + 2 RowCount = 2 FirstRow = RowCount Do While Cells(RowCount, "A") < "" If Cells(RowCount, "C") < _ Cells(RowCount + 1, "C") Then Rows(RowCount + 1).Insert Cells(RowCount + 1, MarketCol).FormulaR1C1 = _ "=Sum(R" & FirstRow & "C" & MarketCol & ":R" & _ RowCount & "C" & MarketCol & ")" Cells(RowCount + 1, MarketCol).Font.Bold = True Cells(FirstRow, firstcol) = _ Cells(RowCount + 1, MarketCol).Value Cells(RowCount + 1, totalcol) = _ Cells(RowCount + 1, MarketCol).Value RowCount = RowCount + 2 FirstRow = RowCount Else RowCount = RowCount + 1 End If Loop 'sort Groups LastRow = Cells(Rows.Count, MarketCol).End(xlUp).Row RowCount = 2 Do While RowCount <= LastRow If Cells(RowCount, firstcol) < "" Then TotalA = Cells(RowCount, firstcol) 'get beginning of next group CompareRow = RowCount + 1 Do While (CompareRow <= LastRow) Do While _ (Cells(CompareRow, firstcol) = "") And _ (CompareRow <= LastRow) CompareRow = CompareRow + 1 Loop If CompareRow <= LastRow Then TotalB = Cells(CompareRow, firstcol) 'if TotalB totalA than insert '2nd group in front of first group If TotalB TotalA Then EndRow = CompareRow Do While Cells(EndRow, totalcol) = "" EndRow = EndRow + 1 Loop Rows(CompareRow & ":" & EndRow).Cut Rows(RowCount).Insert Shift:=xlDown TotalA = TotalB CompareRow = RowCount + 1 Else CompareRow = CompareRow + 1 End If End If Loop End If RowCount = RowCount + 1 Loop 'add blank rows RowCount = LastRow Do While RowCount = 1 If Cells(RowCount, firstcol) < "" Then If RowCount < 2 Then Rows(RowCount).Insert End If End If RowCount = RowCount - 1 Loop 'delete auxilary columns added for running this maacro Columns(totalcol).Delete Columns(firstcol).Delete End Sub Thanks Again, Curt J "Joel" wrote: I forgot the autofit Sub SumAccounts() For Each Sht In Sheets With Sht RowCount = 1 FirstRow = RowCount Do While .Range("B" & RowCount) < "" 'check if account number of Rel Code does not match If .Range("B" & RowCount) < .Range("B" & (RowCount + 1)) Or _ .Range("C" & RowCount) < .Range("C" & (RowCount + 1)) Then 'Insert Two Blank rows .Rows(RowCount + 1).Insert .Rows(RowCount + 1).Insert 'Use worksheet formula to sum column g for Rel code .Range("G" & (RowCount + 1)).Formula = _ "=Sum(G" & FirstRow & ":G" & RowCount & ")" .Range("G" & (RowCount + 1)).Bold = True RowCount = RowCount + 3 FirstRow = RowCount Else RowCount = RowCount + 1 End If Loop End With .Columns("A:S").Columns.AutoFit Next Sht End Sub "Curt J" wrote: I am trying to create a spreadsheet for my work. I'll try to explain what I'm trying to accomplish so you'll have a better understanding of how I'd like the spreadsheet setup. I work for an investment company and we have several investment managers. Each investment manager has their own seprate book of clients. Each separate client account has 19 major variables that I listed below. I will be exporting each investment manager's book of business into excel. Eventually I'll have 60+ tabs so I will need a macro to save time. For this project,I'm trying to find the market value of each client relationship. The market value is simply the dollar amount of each account. The relationship code is a link of seperate accounts that can be viewed togeher as one. There can be as many as 50 accounts within a relationship or as few as 1 account. Here is an example what I'm trying to accomplish: John Smith has an account ($1,000,000), his wife, Joan, has an account ($500,000), and his son, Joe, has an account ($500,000). This family would all have the same relationship code (100). I want a spreadsheet that sums the market value of the three accounts within relationship code 100 for a total of $2,000,000. Jane Doe has an unrelated account. Here relationship code is 200, and her account has a market value of $1,000,000. I want to see that relationship code 100 has a market value of $2,000,000 and relationship code 200 has a market value of 200. These variables will be exported in the same order each time and will always be in cells A1:S1. These variables a Account Name (this will always be cell A1) Account # Rel. Code Type Revocability Alpha Sequence Market Value Prior Yr Revenue YTD Revenue Investment Responsibility Administrator Admin # Investment Officer Inv Officer # Real Estate Officer R.E. Officer # Tax Officer Tax Officer # Client Advisor (This will always be the cell S1) I would like to have all of the data sorted and grouped by the category "Rel. Code" which is always in column C. Once the data is organized I would then like a formula that sums the market value (column G) of all of the accounts that have the same "Rel. Code". When the market value is summed, I would like that value to be in bold. I would then like a blank row inserted below. In the Smith example above, the data will be exported into excel as follows: John Smith (rel code in C2) (market value in G2) Joan Smith (rel code in C3) (market value in G3) Joe Smith (rel code in C4) (market value in G4) Jane Doe (rel code in C5) (market value in G5) After the macro is applied, I would like the shreadsheet to be setup like this: John Smith (rel code in C2) (market value in G2) Joan Smith (rel code in C3) (market value in G3) Joe Smith (rel code in C4) (market value in G4) G5 will have the sum of the market values of G2,G3, and G4 in bold; all other cells in row 5 will be blank. All cells in row 6 will be blank. Jane Doe (rel code in C7) (market value in G7) G8 will have the sum of the market value of G7 in bold; all other cells in row 8 will be blank. All cells in row 9 will be blank. Once this is done, please autofit the columns in the spreadsheet. Thank you all of your help, I hope I was clear in describing this spreadsheet. Please let me know if you have any other questions. Curt J. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Formula Help
Thanks for all of your help!
have a nice day, Curt "Joel" wrote: A suggested improvement. When sorting the groups to get the last row of the group use end(xldown). You already put a space between the groups and the last row will be the last row of data before the empty row 'get beginning of next group EndGroup = Range("C" & Rowcount).End(xldown).Row 'Start of new Group StartofGroup = EndGroup + 2 "Curt J" wrote: Hi Joel, Thank you for looking into my problem. Unfortunatly, I haven't been able to get this to work yet. I am receiving the message "Run time Error '438': Object doesn't support this property or method." In case this helps you, I was given the following code below in the past. It ranks the rel codes by market value in descending order, and deletes some columns that were previously found to be useless. I tested this code and 8 different sheets. It worked for 7 of them, but on one of them it gave me the error message 13. Type mismatch. The code is: Sub fixworksheet() 'Delete unused rows ColCount = 1 Do While Cells(1, ColCount) < "" Heading = Cells(1, ColCount) Select Case Heading Case "Alpha Sequence", _ "Administrator", _ "Admin #", _ "Investment Officer", _ "Inv Officer #", _ "Real Estate Officer", _ "R.E. Officer #", _ "Tax Officer", _ "Tax Officer #" Columns(ColCount).Delete Case Else ColCount = ColCount + 1 End Select Loop 'Sort data by Rel. code Column LastRow = Cells(Rows.Count, "A").End(xlUp).Row LastCol = Cells(1, Columns.Count).End(xlToLeft).Column Set SortRange = Range("A2", Cells(LastRow, LastCol)) SortRange.Sort _ Key1:=Range("C2"), _ Order1:=xlAscending, _ Header:=xlGuess, _ MatchCase:=False, _ Orientation:=xlTopToBottom 'Find Market Value column Set Market = Rows(1).Find(what:="Market Value", LookIn:=xlValues) MarketCol = Market.Column 'Insert totals 'Plase total in first empty column where each group starts firstcol = LastCol + 1 'Place total in 2nd empty column where each group ends totalcol = LastCol + 2 RowCount = 2 FirstRow = RowCount Do While Cells(RowCount, "A") < "" If Cells(RowCount, "C") < _ Cells(RowCount + 1, "C") Then Rows(RowCount + 1).Insert Cells(RowCount + 1, MarketCol).FormulaR1C1 = _ "=Sum(R" & FirstRow & "C" & MarketCol & ":R" & _ RowCount & "C" & MarketCol & ")" Cells(RowCount + 1, MarketCol).Font.Bold = True Cells(FirstRow, firstcol) = _ Cells(RowCount + 1, MarketCol).Value Cells(RowCount + 1, totalcol) = _ Cells(RowCount + 1, MarketCol).Value RowCount = RowCount + 2 FirstRow = RowCount Else RowCount = RowCount + 1 End If Loop 'sort Groups LastRow = Cells(Rows.Count, MarketCol).End(xlUp).Row RowCount = 2 Do While RowCount <= LastRow If Cells(RowCount, firstcol) < "" Then TotalA = Cells(RowCount, firstcol) 'get beginning of next group CompareRow = RowCount + 1 Do While (CompareRow <= LastRow) Do While _ (Cells(CompareRow, firstcol) = "") And _ (CompareRow <= LastRow) CompareRow = CompareRow + 1 Loop If CompareRow <= LastRow Then TotalB = Cells(CompareRow, firstcol) 'if TotalB totalA than insert '2nd group in front of first group If TotalB TotalA Then EndRow = CompareRow Do While Cells(EndRow, totalcol) = "" EndRow = EndRow + 1 Loop Rows(CompareRow & ":" & EndRow).Cut Rows(RowCount).Insert Shift:=xlDown TotalA = TotalB CompareRow = RowCount + 1 Else CompareRow = CompareRow + 1 End If End If Loop End If RowCount = RowCount + 1 Loop 'add blank rows RowCount = LastRow Do While RowCount = 1 If Cells(RowCount, firstcol) < "" Then If RowCount < 2 Then Rows(RowCount).Insert End If End If RowCount = RowCount - 1 Loop 'delete auxilary columns added for running this maacro Columns(totalcol).Delete Columns(firstcol).Delete End Sub Thanks Again, Curt J "Joel" wrote: I forgot the autofit Sub SumAccounts() For Each Sht In Sheets With Sht RowCount = 1 FirstRow = RowCount Do While .Range("B" & RowCount) < "" 'check if account number of Rel Code does not match If .Range("B" & RowCount) < .Range("B" & (RowCount + 1)) Or _ .Range("C" & RowCount) < .Range("C" & (RowCount + 1)) Then 'Insert Two Blank rows .Rows(RowCount + 1).Insert .Rows(RowCount + 1).Insert 'Use worksheet formula to sum column g for Rel code .Range("G" & (RowCount + 1)).Formula = _ "=Sum(G" & FirstRow & ":G" & RowCount & ")" .Range("G" & (RowCount + 1)).Bold = True RowCount = RowCount + 3 FirstRow = RowCount Else RowCount = RowCount + 1 End If Loop End With .Columns("A:S").Columns.AutoFit Next Sht End Sub "Curt J" wrote: I am trying to create a spreadsheet for my work. I'll try to explain what I'm trying to accomplish so you'll have a better understanding of how I'd like the spreadsheet setup. I work for an investment company and we have several investment managers. Each investment manager has their own seprate book of clients. Each separate client account has 19 major variables that I listed below. I will be exporting each investment manager's book of business into excel. Eventually I'll have 60+ tabs so I will need a macro to save time. For this project,I'm trying to find the market value of each client relationship. The market value is simply the dollar amount of each account. The relationship code is a link of seperate accounts that can be viewed togeher as one. There can be as many as 50 accounts within a relationship or as few as 1 account. Here is an example what I'm trying to accomplish: John Smith has an account ($1,000,000), his wife, Joan, has an account ($500,000), and his son, Joe, has an account ($500,000). This family would all have the same relationship code (100). I want a spreadsheet that sums the market value of the three accounts within relationship code 100 for a total of $2,000,000. Jane Doe has an unrelated account. Here relationship code is 200, and her account has a market value of $1,000,000. I want to see that relationship code 100 has a market value of $2,000,000 and relationship code 200 has a market value of 200. These variables will be exported in the same order each time and will always be in cells A1:S1. These variables a Account Name (this will always be cell A1) Account # Rel. Code Type Revocability Alpha Sequence Market Value Prior Yr Revenue YTD Revenue Investment Responsibility Administrator Admin # Investment Officer Inv Officer # Real Estate Officer R.E. Officer # Tax Officer Tax Officer # Client Advisor (This will always be the cell S1) I would like to have all of the data sorted and grouped by the category "Rel. Code" which is always in column C. Once the data is organized I would then like a formula that sums the market value (column G) of all of the accounts that have the same "Rel. Code". When the market value is summed, I would like that value to be in bold. I would then like a blank row inserted below. In the Smith example above, the data will be exported into excel as follows: John Smith (rel code in C2) (market value in G2) Joan Smith (rel code in C3) (market value in G3) Joe Smith (rel code in C4) (market value in G4) Jane Doe (rel code in C5) (market value in G5) After the macro is applied, I would like the shreadsheet to be setup like this: John Smith (rel code in C2) (market value in G2) Joan Smith (rel code in C3) (market value in G3) Joe Smith (rel code in C4) (market value in G4) G5 will have the sum of the market values of G2,G3, and G4 in bold; all other cells in row 5 will be blank. All cells in row 6 will be blank. Jane Doe (rel code in C7) (market value in G7) G8 will have the sum of the market value of G7 in bold; all other cells in row 8 will be blank. All cells in row 9 will be blank. Once this is done, please autofit the columns in the spreadsheet. Thank you all of your help, I hope I was clear in describing this spreadsheet. Please let me know if you have any other questions. Curt J. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Formula Help
I'm looking at you code and I think there is a better way of doing what you
are trying to do. It may solve you error also. I think the error is being cause by the CUT when you are doing the sort. If you use the dort method below you won't have to cut. 1) Add Group Total values to an auxilary column X in every row. Use the code I provided with your modification but instead of inserting row and putting the totals in the new rows; Add the totals to Column X. from If Cells(RowCount, "C") < _ Cells(RowCount + 1, "C") Then Rows(RowCount + 1).Insert Cells(RowCount + 1, MarketCol).FormulaR1C1 = _ "=Sum(R" & FirstRow & "C" & MarketCol & ":R" & _ RowCount & "C" & MarketCol & ")" Cells(RowCount + 1, MarketCol).Font.Bold = True Cells(FirstRow, firstcol) = _ Cells(RowCount + 1, MarketCol).Value Cells(RowCount + 1, totalcol) = _ Cells(RowCount + 1, MarketCol).Value RowCount = RowCount + 2 FirstRow = RowCount Else RowCount = RowCount + 1 End If to If Cells(RowCount, "C") < _ Cells(RowCount + 1, "C") Then 'Get the total Range("X" & FirstRow).FormulaR1C1 = _ "=Sum(R" & FirstRow & "C" & MarketCol & ":R" & _ RowCount & "C" & MarketCol & ")" 'Paste total as values in auxilary column Range("X" & RowCount).copy Range("X" & FirstRow).PasteSpecial paste:=xlPasteValues FirstRow = RowCount + 1 end if RowCount = RowCount + 1 2) Now sort yoiur data using 3 keys a) Total in column X b) Account # c) Group 3) Now go Back and insert 2 rows between the groups and add the subtotals t the first new row. "Joel" wrote: A suggested improvement. When sorting the groups to get the last row of the group use end(xldown). You already put a space between the groups and the last row will be the last row of data before the empty row 'get beginning of next group EndGroup = Range("C" & Rowcount).End(xldown).Row 'Start of new Group StartofGroup = EndGroup + 2 "Curt J" wrote: Hi Joel, Thank you for looking into my problem. Unfortunatly, I haven't been able to get this to work yet. I am receiving the message "Run time Error '438': Object doesn't support this property or method." In case this helps you, I was given the following code below in the past. It ranks the rel codes by market value in descending order, and deletes some columns that were previously found to be useless. I tested this code and 8 different sheets. It worked for 7 of them, but on one of them it gave me the error message 13. Type mismatch. The code is: Sub fixworksheet() 'Delete unused rows ColCount = 1 Do While Cells(1, ColCount) < "" Heading = Cells(1, ColCount) Select Case Heading Case "Alpha Sequence", _ "Administrator", _ "Admin #", _ "Investment Officer", _ "Inv Officer #", _ "Real Estate Officer", _ "R.E. Officer #", _ "Tax Officer", _ "Tax Officer #" Columns(ColCount).Delete Case Else ColCount = ColCount + 1 End Select Loop 'Sort data by Rel. code Column LastRow = Cells(Rows.Count, "A").End(xlUp).Row LastCol = Cells(1, Columns.Count).End(xlToLeft).Column Set SortRange = Range("A2", Cells(LastRow, LastCol)) SortRange.Sort _ Key1:=Range("C2"), _ Order1:=xlAscending, _ Header:=xlGuess, _ MatchCase:=False, _ Orientation:=xlTopToBottom 'Find Market Value column Set Market = Rows(1).Find(what:="Market Value", LookIn:=xlValues) MarketCol = Market.Column 'Insert totals 'Plase total in first empty column where each group starts firstcol = LastCol + 1 'Place total in 2nd empty column where each group ends totalcol = LastCol + 2 RowCount = 2 FirstRow = RowCount Do While Cells(RowCount, "A") < "" If Cells(RowCount, "C") < _ Cells(RowCount + 1, "C") Then Rows(RowCount + 1).Insert Cells(RowCount + 1, MarketCol).FormulaR1C1 = _ "=Sum(R" & FirstRow & "C" & MarketCol & ":R" & _ RowCount & "C" & MarketCol & ")" Cells(RowCount + 1, MarketCol).Font.Bold = True Cells(FirstRow, firstcol) = _ Cells(RowCount + 1, MarketCol).Value Cells(RowCount + 1, totalcol) = _ Cells(RowCount + 1, MarketCol).Value RowCount = RowCount + 2 FirstRow = RowCount Else RowCount = RowCount + 1 End If Loop 'sort Groups LastRow = Cells(Rows.Count, MarketCol).End(xlUp).Row RowCount = 2 Do While RowCount <= LastRow If Cells(RowCount, firstcol) < "" Then TotalA = Cells(RowCount, firstcol) 'get beginning of next group CompareRow = RowCount + 1 Do While (CompareRow <= LastRow) Do While _ (Cells(CompareRow, firstcol) = "") And _ (CompareRow <= LastRow) CompareRow = CompareRow + 1 Loop If CompareRow <= LastRow Then TotalB = Cells(CompareRow, firstcol) 'if TotalB totalA than insert '2nd group in front of first group If TotalB TotalA Then EndRow = CompareRow Do While Cells(EndRow, totalcol) = "" EndRow = EndRow + 1 Loop Rows(CompareRow & ":" & EndRow).Cut Rows(RowCount).Insert Shift:=xlDown TotalA = TotalB CompareRow = RowCount + 1 Else CompareRow = CompareRow + 1 End If End If Loop End If RowCount = RowCount + 1 Loop 'add blank rows RowCount = LastRow Do While RowCount = 1 If Cells(RowCount, firstcol) < "" Then If RowCount < 2 Then Rows(RowCount).Insert End If End If RowCount = RowCount - 1 Loop 'delete auxilary columns added for running this maacro Columns(totalcol).Delete Columns(firstcol).Delete End Sub Thanks Again, Curt J "Joel" wrote: I forgot the autofit Sub SumAccounts() For Each Sht In Sheets With Sht RowCount = 1 FirstRow = RowCount Do While .Range("B" & RowCount) < "" 'check if account number of Rel Code does not match If .Range("B" & RowCount) < .Range("B" & (RowCount + 1)) Or _ .Range("C" & RowCount) < .Range("C" & (RowCount + 1)) Then 'Insert Two Blank rows .Rows(RowCount + 1).Insert .Rows(RowCount + 1).Insert 'Use worksheet formula to sum column g for Rel code .Range("G" & (RowCount + 1)).Formula = _ "=Sum(G" & FirstRow & ":G" & RowCount & ")" .Range("G" & (RowCount + 1)).Bold = True RowCount = RowCount + 3 FirstRow = RowCount Else RowCount = RowCount + 1 End If Loop End With .Columns("A:S").Columns.AutoFit Next Sht End Sub "Curt J" wrote: I am trying to create a spreadsheet for my work. I'll try to explain what I'm trying to accomplish so you'll have a better understanding of how I'd like the spreadsheet setup. I work for an investment company and we have several investment managers. Each investment manager has their own seprate book of clients. Each separate client account has 19 major variables that I listed below. I will be exporting each investment manager's book of business into excel. Eventually I'll have 60+ tabs so I will need a macro to save time. For this project,I'm trying to find the market value of each client relationship. The market value is simply the dollar amount of each account. The relationship code is a link of seperate accounts that can be viewed togeher as one. There can be as many as 50 accounts within a relationship or as few as 1 account. Here is an example what I'm trying to accomplish: John Smith has an account ($1,000,000), his wife, Joan, has an account ($500,000), and his son, Joe, has an account ($500,000). This family would all have the same relationship code (100). I want a spreadsheet that sums the market value of the three accounts within relationship code 100 for a total of $2,000,000. Jane Doe has an unrelated account. Here relationship code is 200, and her account has a market value of $1,000,000. I want to see that relationship code 100 has a market value of $2,000,000 and relationship code 200 has a market value of 200. These variables will be exported in the same order each time and will always be in cells A1:S1. These variables a Account Name (this will always be cell A1) Account # Rel. Code Type Revocability Alpha Sequence Market Value Prior Yr Revenue YTD Revenue Investment Responsibility Administrator Admin # Investment Officer Inv Officer # Real Estate Officer R.E. Officer # Tax Officer Tax Officer # Client Advisor (This will always be the cell S1) I would like to have all of the data sorted and grouped by the category "Rel. Code" which is always in column C. Once the data is organized I would then like a formula that sums the market value (column G) of all of the accounts that have the same "Rel. Code". When the market value is summed, I would like that value to be in bold. I would then like a blank row inserted below. In the Smith example above, the data will be exported into excel as follows: John Smith (rel code in C2) (market value in G2) Joan Smith (rel code in C3) (market value in G3) Joe Smith (rel code in C4) (market value in G4) Jane Doe (rel code in C5) (market value in G5) After the macro is applied, I would like the shreadsheet to be setup like this: John Smith (rel code in C2) (market value in G2) Joan Smith (rel code in C3) (market value in G3) Joe Smith (rel code in C4) (market value in G4) G5 will have the sum of the market values of G2,G3, and G4 in bold; all other cells in row 5 will be blank. All cells in row 6 will be blank. Jane Doe (rel code in C7) (market value in G7) G8 will have the sum of the market value of G7 in bold; all other cells in row 8 will be blank. All cells in row 9 will be blank. Once this is done, please autofit the columns in the spreadsheet. Thank you all of your help, I hope I was clear in describing this spreadsheet. Please let me know if you have any other questions. Curt J. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula | Excel Worksheet Functions | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |