Writing loop to create arrays of data associate with like strings with the order of the elements based on loop counter

[JR_Chicago]

Writing loop to create arrays of data associated with like strings with the order of the elements based on loop counter

I have data orientated as follows:

Column A: Header: Product Name (i.e. string data type) ex. "stockA"
Column B: Header: Buy / Sell (i.e. string data type) ex. "Buy" or "Sell"
Column C: Header: Qty Traded (i.e. integer data type) ex. 20
Column D: Header: Price (i.e. integer data type) ex. 712.5
Column E: Header: Trade Date (i.e. date data type) ex. 12/1/09

I have filtered the data so that all the products are in alphabetical ascending order, and all "Buy" trades are in one table and all "Sell" trades are in another table

Example of table:

Buy Table:
stockA Buy 20 712.5 12/1/09
stockA Buy 30 712.0 12/1/09
stockB Buy 10 200.5 12/1/09
stockB Buy 5 200.0 12/1/09

Sell Table:
stockA Sell 20 713.0 12/1/09
stockA Sell 30 713.5 12/1/09
stockB Sell 10 200.5 12/1/09
stockB Sell 5 200.5 12/1/09

I need to write a macro that:
1) Looks at the string in Column A and
2) Creates an array of:
a) Qty traded (on Buy and Sell sides)
b) Trade Prices
Until the string in Column A changes, then, start building new arrays for that product:

I need help writing a loop that will go through Column A, and build the Qty Traded and Trade Price arrays, for each stock in Column A.

Ex. At that at the end of the loop:

qtyBoughtStockA(), tradeBuyStockA()
AND
qtySoldStockA(), tradeSellStockA()

have been created.

The elements of both arrays should be in the order of their occurence.

I will later use the elements in these arrays to compute, Average Trade Price, Total contracts traded, Gross and Net Profits, etc.

Can anyone help?

I'm more than willing to provide any further info that is need to help you visualize the problem.

Thanks.

 

[SamTYler]

See if this gives you any ideas

Enum TransactionType
   ttBuy = 1
   ttBuyTrade = 2 'Below, if taAction > 0 was buy
   ttSell = -1    '       if taAction < 0 was sell
   ttSellTrade = -2
End Enum

Type Transaction
   taProduct As String
   taAction As TransactionType
   taQty As Long
   taPrice As Currency 'Limited to two decimals. Use Single for more decimals
   taTransDate As Date
End Type

Sub SomethingOrOther()

Dim SomeArray() As Transaction

'Start RowCrawler Loop
Yadayada
With SomeArray(j)
   .taProduct = "IBM"
   .taAction = ttBuy
   .taQty = 1000
   .taPrice = 1.37
   .taDate = "4/1/65"
End With
Yadayada
'Next Rowcrawler

End Sub

</PRE>

 

[SamTYler]

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> RowCrawler()<br><SPAN style="color:#007F00">'A row crawler "crawls" down the rows by blocks of</SPAN><br><SPAN style="color:#007F00">'contiguous identical cell values. The data must be</SPAN><br><SPAN style="color:#007F00">'sorted on the column to be traversed.</SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> J <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> <SPAN style="color:#007F00">'Counter</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> <SPAN style="color:#007F00">'To end the sub or main loop</SPAN><br>   <SPAN style="color:#007F00">'Set LastRow = ???</SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> StartCel <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> EndCel <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#007F00">'Initialize StartCel</SPAN><br>   <SPAN style="color:#00007F">Set</SPAN> StartCel = Range("A1") <SPAN style="color:#007F00">'Change to suit</SPAN><br>   <br><SPAN style="color:#007F00">'Outer loop until LastRow</SPAN><br>   <SPAN style="color:#007F00">'Initialize EndCel</SPAN><br>   <SPAN style="color:#00007F">Set</SPAN> EndCel = StartCel<br><br>   <SPAN style="color:#007F00">'Set EndCel to end row of block</SPAN><br>   <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> EndCel.Offset(1, 0).Value = StartCel.Value<br>      <SPAN style="color:#00007F">Set</SPAN> EndCel = EndCel.Offset(1, 0)<br>   <SPAN style="color:#00007F">Wend</SPAN><br>   <br>   <SPAN style="color:#007F00">'Block Loop</SPAN><br>   <SPAN style="color:#00007F">For</SPAN> J = 0 <SPAN style="color:#00007F">To</SPAN> (<SPAN style="color:#00007F">End</SPAN>Cel.Row - StartCel.Row)<br>      <SPAN style="color:#00007F">With</SPAN> StartCel.Offset(J, 0)<br>      <SPAN style="color:#007F00">'Do stuff with block of rows with equal</SPAN><br>      <SPAN style="color:#007F00">'values in column A</SPAN><br>      <SPAN style="color:#00007F">Wend</SPAN><br>   <SPAN style="color:#00007F">Next</SPAN> J<br>   <br><SPAN style="color:#007F00">'Set StartCel to start of next block</SPAN><br><SPAN style="color:#00007F">Set</SPAN> StartCel = EndCel.Offset(1, 0)<br><br><SPAN style="color:#007F00">'Next Outer Loop. Typ; StartCel = LastRow + 1</SPAN><br>End <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[JR_Chicago]

@ SamTYler'

I took a look at your code and it definitely generates some ideas as well as some further questions...

Please send me a private message if / when you become avail today so maybe we converse further on the topic.

Cheers,

JR