VBA Formula Help

C

Curt J

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 are:

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.
 
J

Jim Cone

RE: "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 are:

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.
 
C

Curt J

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.
 
J

Joel

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
 
J

Joel

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
 
C

Curt J

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
 
J

Joel

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
 
J

Joel

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
 
J

Joel

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro Help 7
Comparing values or text in 2 different workbooks 2
Recording Macro - Insert Formula 2
Summing values... conditionally 3
Help with ComboBox in VBA 1
simple yet complex scenario - goal seek problem 1
Sumproduct formula help 2
Dyson vs Shark Vacuum Cleaners 19

Top