TEST_MACRO_insert row on value change and calc

[sumesh56]

In the worksheet I want to calculate the bifurcated numbers belonging to one share. have to insert a row after every valuechange based on ( SECURITY (COL F) TRADE TYPE(COL G) and calculate the qty,rate and the total price of each symbol tradetype wise ,ie,buy or sell. pls have a look at the second sheet in which i have illustrated the outcome. I have attached a macro which works partially. kindly have an edit on the macro so that it should work perfectly in the expected route. I have noted the points to be corrected with arrow...thanks in advance.



ATTACHED
1. ORGINAL FILE-edited 100722- version
2. MACRO ENABLED FILE
3. ERROR TO BE CORRECTED IN THE CODE FILE---Named code does not work properly

Sub rsnMacro()
Dim lngR As Long
Dim iC As Integer

lngR = 6
iC = 1

While Cells(lngR, 6).Value <> ""
If Cells(lngR, 6).Value <> Cells(lngR + 1, 6).Value Or Cells(lngR, 7).Value <> Cells(lngR + 1, 7).Value Then
'1.Insert a row after every value change in col f (symbol) as well as col g (trade date)
lngR = lngR + 1
Rows(lngR).Insert
'2.in the new row type COMBO in col A
Cells(lngR, 1).Value = "COMBO"
With Cells(lngR, 1).Resize(1, 12)
'3.highlight the contents in the new row with color yello and charaters in color red.
.Interior.ColorIndex = 6
.Font.ColorIndex = 3
'4.in the new row copy paste the contents from the just above row.from col D:G
Intersect(.Cells, Range("B:G")).Value = Intersect(.Cells, Range("B:G")).Offset(-1).Value
Intersect(.Cells, Range("K:K")).Value = Intersect(.Cells, Range("K:K")).Offset(-1).Value


'5.calculate the sum of a cluster(Same symbol, same date)in col H, else input the contents from the above ROW.
'Have to make calculations in col H (sum total) in column I calculate average price AND in col J = sum QTY* PRICE.
'7. in col j the formula should work. Qty* PRICE
.Cells(1, 8).FormulaR1C1 = "=SUM(R[-" & iC & "]C:R[-1]C)"
.Cells(1, 10).FormulaR1C1 = "=SUM(R[-" & iC & "]C:R[-1]C)"
.Cells(1, 9).FormulaR1C1 = "=RC[1]/RC[-1]"
End With
iC = 1
lngR = lngR + 1
Else
iC = iC + 1
lngR = lngR + 1
End If
Wend

End Sub

[oeldere]

NON VBA solution

With a pivot table to analyse the data.

See the attached file.

[sumesh56]

oeldere ,thanks for the suggestion. this is not certainly what i wanted.