Function or VBA Script Help

[aeddipa]

I'm not sure if I can do this with functions that are in excel or if I need to build a VBA script to perform this operation.

I want to be able to sell a certain amount of stock from different lots I own based on having to raise (take out of my account for other purposes) cash.

If the amount to sell is higher than the lot(lot = row), I need the remaining balance to drop to the next lot and insert it there.

Here is an Example:

NB - Acct

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: ARIAL,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 70px"><COL style="WIDTH: 279px"><COL style="WIDTH: 86px"><COL style="WIDTH: 65px"><COL style="WIDTH: 84px"><COL style="WIDTH: 25px"><COL style="WIDTH: 76px"><COL style="WIDTH: 93px"><COL style="WIDTH: 108px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>F</TD><TD>G</TD><TD>S</TD><TD>T</TD><TD>V</TD><TD>W</TD><TD>X</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-FAMILY: Arial">Quantity</TD><TD style="FONT-FAMILY: Arial">Security Description</TD><TD>Market Value</TD><TD>% of Type</TD><TD>Cusip</TD><TD>Lot</TD><TD>Cusip</TD><TD style="FONT-FAMILY: Arial">Shares To Sell</TD><TD style="FONT-FAMILY: Arial">Allocation of Lots</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">473.00 </TD><TD>ABBOTT LABORATORIES</TD><TD style="TEXT-ALIGN: right">23,399.31 </TD><TD style="TEXT-ALIGN: right">0.55% </TD><TD>002824-10-0</TD><TD style="TEXT-ALIGN: right">16 </TD><TD style="TEXT-ALIGN: right">002824100</TD><TD style="TEXT-ALIGN: right">500</TD><TD style="TEXT-ALIGN: right">473</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">600.00 </TD><TD>ABBOTT LABORATORIES</TD><TD style="TEXT-ALIGN: right">29,682.00 </TD><TD style="TEXT-ALIGN: right">0.70% </TD><TD>002824-10-0</TD><TD style="TEXT-ALIGN: right">17 </TD><TD style="TEXT-ALIGN: right">002824100</TD><TD> </TD><TD style="TEXT-ALIGN: right">27</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">3.00 </TD><TD>ABBOTT LABORATORIES</TD><TD style="TEXT-ALIGN: right">148.41 </TD><TD style="TEXT-ALIGN: right">0.00% </TD><TD>002824-10-0</TD><TD style="TEXT-ALIGN: right">25 </TD><TD style="TEXT-ALIGN: right">002824100</TD><TD> </TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">494.00 </TD><TD>ABBOTT LABORATORIES</TD><TD style="TEXT-ALIGN: right">24,438.18 </TD><TD style="TEXT-ALIGN: right">0.58% </TD><TD>002824-10-0</TD><TD style="TEXT-ALIGN: right">26 </TD><TD style="TEXT-ALIGN: right">002824100</TD><TD> </TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">1,742.00 </TD><TD>ADOBE SYSTEMS INC</TD><TD style="TEXT-ALIGN: right">57,555.68 </TD><TD style="TEXT-ALIGN: right">1.36% </TD><TD>00724F-10-1</TD><TD style="TEXT-ALIGN: right">18 </TD><TD>00724F101</TD><TD style="TEXT-ALIGN: right">589</TD><TD style="TEXT-ALIGN: right">589 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">993.00 </TD><TD>ADOBE SYSTEMS INC</TD><TD style="TEXT-ALIGN: right">32,808.72 </TD><TD style="TEXT-ALIGN: right">0.78% </TD><TD>00724F-10-1</TD><TD style="TEXT-ALIGN: right">19 </TD><TD>00724F101</TD><TD> </TD><TD style="TEXT-ALIGN: right">0</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 9pt; WIDTH: 332px; FONT-FAMILY: Arial; HEIGHT: 86px" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>W10</TD><TD>=VLOOKUP(V10,'NB - Advent'!$D$10:$L$74,9,FALSE)</TD></TR><TR><TD>W6</TD><TD>=VLOOKUP(V6,'NB - Advent'!$D$10:$L$74,9,FALSE)</TD></TR></TBODY></TABLE>
So as we can see, I want to sell 500 shares from Abbott Lab. In the first lot, I only have 473 shares, so I sell that amount (the MIN of the two). Then, I take the remaining shares and apply the same method. Since 27 < 600 shares, I will sell 27 more shares and leave the remaining 2 lots alone.

So what I would like to know is if there is an easy way to identify each fund (probably by ID AKA Cusip) and find out where I need to sell the shares. Remember, I cannot just copy the VLOOKUP formula in Column W or it will show 500 shares to sell for each lot. The 500 is the TOTAL.

If anybody could help me with this so that I may sell the amount of shares to the correct lot, that would be greatly appreciated.

I assume it can be done, but am having a hard time thinking of how to structure it in VBA let alone functions.

Thanks in advance!

 

[aeddipa]

This is what I have so far.

The script works, but occurs for every cell.

How can I specify to take the remaining balance of the first row for that fund and apply it to the below line and if the shares to sell are less than the lot place 0's in the remaining rows of that fund?


Sub Selling()
Dim iRow As Integer
Dim rng As Range
Dim myVal As String
Set rng = Range("I6:I150")
myVal = "Shares to Sell"
With Columns("I")
Set Found = .Find(What:=myVal, after:=.Cells(1, 1), LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False, SearchDirection:=xlNext)
trancode = Found.Address
With Found
For iRow = 1 To rng.Rows.Count
.Offset(iRow, 0).Value = WorksheetFunction.VLookup(.Offset(iRow + 1, -1), Sheets("NB - Advent").Range("D10:L74"), 9, False)
If .Offset(iRow, 0).Value > .Offset(iRow, -8).Value Then
.Offset(iRow, 0).Value = .Offset(iRow, -8).Value
Else
.Offset(iRow, 0).Value = WorksheetFunction.VLookup(.Offset(iRow + 1, -1), Sheets("NB - Advent").Range("D10:L74"), 9, False)
If .Offset(iRow, -8).Value - .Offset(iRow, 0).Value < 0 Then
.Offset(iRow, 1).Value = 0
Else
.Offset(iRow, 1).Value = .Offset(iRow, -8).Value - .Offset(iRow, 0).Value
On Error Resume Next
End If
End If
Next iRow
End With
End With
Columns("I:J").Select
Selection.NumberFormat = "#,##0"
End Sub