Another Excel coding riddle

[kdigital]

This was introduced to me. So, I thought I would bring it to you to make sure I wasn't making it too complicated. Ok Basically data would be copy and pasted into a worksheet. The worksheet would look something like this.

Security info1 info2 price industry info3
MRKjdjdjdjdjdjd4DrugjdjdTWXdjdjdjdj10MediajdjdHNZdkdkddkdk4fooddkdkVIAkdkddkdk4mediakdkdKFTdkdkddkdk20fooddkdkdMUdkdkddkdk40TechnologydkdkdLLYdjdjdjdj30drugdkdkdTXNdjdjddjdjd30Technologydjdjd

So the idea is for excel to sort the column with industry in it.
Then a row is inserted between each industry and the price column for those securities are summed.

On the surface it sounds simple but then while looking at it of course it became more cumbersome.

I'm thinking if we break it down in sections first you have to identify the range as the industry column. Then sort the range.

After the Range has been sorted next you have to take a look at the column for the same industry. IF the same industry is there then you have to countdown to the bottom and insert a row.

after the row is inserted you move over to the price column and sum it.

Now how do we put that in syntax for vba code.

[kdigital]

oops suppose I should have used a table for that...well I included a sample xls file.

[lucas]

I can get you started on this. The hard part is the subtotals but I have provided a partial solution. You have to pick each cell and hit the button to get your subtotals......clearer description in the attachment.

If I get a chance I will look into automating that part but that's all I have for now. Maybe someone else will take a look at it with us.




Edit: I had to add a column to get the subtotals to work.....but if that's a problem I think we can fix that....

[Bob Phillips]

[vba]

Sub SortData()
Dim IndustryCol As Long

On Error Resume Next
IndustryCol = Application.Match("Industry", Rows(1), 0)
On Error GoTo 0
If IndustryCol > 0 Then

ActiveSheet.UsedRange.Sort key1:=Cells(1, IndustryCol), order1:=xlAscending, header:=xlYes
End If
End Sub
[/vba]

[Bob Phillips]

With subtotals

[vba]

Sub SortData()
Dim IndustryCol As Long

On Error Resume Next
IndustryCol = Application.Match("Industry", Rows(1), 0)
On Error GoTo 0
If IndustryCol > 0 Then

ActiveSheet.UsedRange.Sort key1:=Cells(1, IndustryCol), order1:=xlAscending, header:=xlYes

Cells.Subtotal GroupBy:=IndustryCol, _
Function:=xlSum, _
TotalList:=Array(IndustryCol - 1), _
Replace:=True, _
PageBreaks:=False, _
SummaryBelowData:=True
End If
End Sub
[/vba]