how do i find unique avg buy prices in multiple group of buys/sell

[John Robbins]

Thanks for looking... (trying to create a macro to go through a report and
create weighted avg for unique groups of data)

I have a report listing all the buys and sells for multiple products. The
report is sorted first by item name, then by transaction type (buy or sell,
so all buys are contiguous). There is a blank row separating each unique
product name.

I want to create a macro to go through all the rows and calculate the
weighted average cost of each product's buys and insert that value into a
column. if product JR has 12 transactions (3 buys 9 sells) I want the
weighted average of the 3 buys inserted into all 12 rows.

Here's my problem, i know how to calculate the weighted avg, but i do not
know how to AUTOMATICALLY create multiple unique weighted avgs for unique
data sets. The blank row between product types (each group of buys and
sells) can be used as an indicator in the iterations, but coding the solution
is beyond my grasp right now.

Check out my a snapshot of my dataset below:

DATE TYPE BUY/SELL QTY PRICE
AVG BUY PX
--------------------------------------------------------------------------------------------
4-May Product X B 10 98.75
?1
4-May Product X B 70 98.50 ?1
4-May Product X S (150) 98.95 ?1

4-May Product y B 20 98.75
?2
4-May Product y B 50 98.65 ?2
4-May Product y S (50) 99.05 ?2


"?1" and "?2" should be distinct, unique values.

 

[John Robbins]

Important to note that the number of buys/sells for each product type (rows
per product type) can be anywhere from 2 to upwards of 50.
Thanks