VBA/Range

[chintan desai]

Hi,

I am looking for some VBA help. I am working on a project that consists of an excel sheet that has buy/sell trades for certain stocks. I have posted a sample below.

<table width="573" border="0" cellpadding="0" cellspacing="0"><colgroup><col width="178"><col span="4" width="64"><col width="75"><col width="64"></colgroup><tbody><tr height="34"> <td class="xl65" style="height:25.5pt;width:134pt" width="178" height="34">Symbol </td> <td class="xl65" style="width:48pt" width="64">Side </td> <td class="xl65" style="width:48pt" width="64">Filled </td> <td class="xl65" style="width:48pt" width="64">signed qty</td> <td class="xl65" style="width:48pt" width="64">Cum Pos</td> <td class="xl66" style="width:56pt" width="75"> Lowest cost </td> <td class="xl65" style="width:48pt" width="64">AvgPx </td> </tr></tbody></table>
<table width="573" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:6509;width:134pt" width="178"> <col style="width:48pt" span="4" width="64"> <col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:13.5pt" height="18"> <td class="xl65" style="height:13.5pt;width:134pt" width="178" height="18">AAPL</td> <td class="xl66" style="width:48pt" width="64">Buy</td> <td class="xl65" style="width:48pt" width="64" align="right">1000</td> <td class="xl68" style="width:48pt" width="64" align="right">1000</td> <td class="xl68" style="width:48pt" width="64" align="right">1,000</td> <td class="xl69" style="width:56pt" width="75"> $356.7
</td> <td class="xl65" style="width:48pt" width="64" align="right">356.7</td> </tr> <tr style="height:13.5pt" height="18"> <td class="xl65" style="height:13.5pt;width:134pt" width="178" height="18">AAPL</td> <td class="xl71" style="width:48pt" width="64">Sell</td> <td class="xl65" style="width:48pt" width="64" align="right">500</td> <td class="xl68" style="width:48pt" width="64" align="right">-500</td> <td class="xl68" style="width:48pt" width="64" align="right">500</td> <td class="xl69" style="width:56pt" width="75">
</td> <td class="xl65" style="width:48pt" width="64" align="right">356.99</td> </tr> <tr style="height:13.5pt" height="18"> <td class="xl65" style="height:13.5pt;width:134pt" width="178" height="18">AAPL</td> <td class="xl71" style="width:48pt" width="64">Sell</td> <td class="xl65" style="width:48pt" width="64" align="right">500</td> <td class="xl68" style="width:48pt" width="64" align="right">-500</td> <td class="xl68" style="width:48pt" width="64" align="right">0</td> <td class="xl69" style="width:56pt" width="75">
</td> <td class="xl65" style="width:48pt" width="64" align="right">357.4</td> </tr> <tr style="height:13.5pt" height="18"> <td class="xl65" style="height:13.5pt;width:134pt" width="178" height="18">AAPL</td> <td class="xl66" style="width:48pt" width="64">Buy</td> <td class="xl68" style="width:48pt" width="64" align="right">1,000</td> <td class="xl68" style="width:48pt" width="64" align="right">1,000</td> <td class="xl68" style="width:48pt" width="64" align="right">1,000</td> <td class="xl69" style="width:56pt" width="75">
</td> <td class="xl65" style="width:48pt" width="64" align="right">361.78</td> </tr> <tr style="height:13.5pt" height="18"> <td class="xl65" style="height:13.5pt;width:134pt" width="178" height="18">AAPL</td> <td class="xl66" style="width:48pt" width="64">Sell
</td> <td class="xl65" style="width:48pt" width="64" align="right">500</td> <td class="xl68" style="width:48pt" width="64" align="right">-500</td> <td class="xl68" style="width:48pt" width="64" align="right">500</td> <td class="xl69" style="width:56pt" width="75">
</td> <td class="xl65" style="width:48pt" width="64" align="right">361.34</td> </tr> </tbody></table>

So if you notice, the lowest column is blank because that is what i am trying to fill automatically. So for example, I boguht 1000 shares of apple and my avg price was 356.7 because that is the lowest price relative to where I bought the stock. When I sell the stock, 500 shares, I want the lowest price from the FIRST TWO average prices since those were the recent prices. And going forward, when i sell another 500, I want the lowest price from the last 3 prices. BUT, the other issue here is that whenever the CUM POS column is equal to 0, I have to restart the process of the lowest price search. For example when the "cum pos" is 0 in the third row, when I buy 1000 shares in the 4th row, the lowest price will be the price on the same row ($361.78). And for the 5th row for when I sell 500 shares, I must find the lowest price from the last price and current price.

So over all, I need help to write a vba program that will automate the task of searching for the lowest price when it is a "SELL". When it is a BUY, the program should automatically choose the current average price. Also keep in mind that when the Cum Pos is 0 when the sell occurs, the lowest price for that row is the last x number of rows prior to the previous 0. And the next row starts at new. I hope this makes sense. If not I can try to clear things up. Thanks!

 

[venkat1926]

try this macro

Sub test()
Dim r1 As Range, r2 As Range, r As Range, c As Range, add As String
Set r1 = Range("E2")
Set r2 = Range("E2")
Set r2 = Columns("E:E").Cells.Find(what:=0, lookat:=xlWhole, after:=r2)
'MsgBox r2.Address
add = r2.Address
Set r = Range(r1, r2)
For Each c In r
c.Offset(0, 1) = WorksheetFunction.Min(Range(r1.Offset(0, 2), c.Offset(0, 2)))
Next c
Do
Set r1 = r2.Offset(1, 0)
If r1 = "" Then Exit Do
Set r2 = Columns("E:E").Cells.Find(what:=0, lookat:=xlWhole, after:=r2)
'MsgBox r2.Address
If r2.Address = add Then Set r2 = r1.End(xlDown)
Set r = Range(r1, r2)
'MsgBox r.Address
For Each c In r
c.Offset(0, 1) = WorksheetFunction.Min(Range(r1.Offset(0, 2), c.Offset(0, 2)))
Next c
Loop
Range("F:F").NumberFormat = "0.00"
End Sub

Sub undo()
Range(Range("F2"), Range("F2").End(xlDown)).Cells.Clear
End Sub