I have a problem with "Advanced filter" function in Excel. I use VBA code to create a Advanced filter type of function that
copies data from "Transactions" sheet to "Buy" and "Sell" sheets based on criteria defined in rows "A1:L2" in both " Buys"
and "Sells" worksheets.
However when I use the advanced filter function what I get is all data from Companies
that start with "REL" (REL, RELCAPITAL,RELGOLD,RELIANCE, RELIANCE PETROLEUM ETC ) instead of data from company "REL" alone. I
think this is an Excel problem. Is there a way to overcome this thru programming?
I am attaching the "FIFO_test.xls" file for reference.
The program code is as below
'Create List of Buys
rngTransactions.AdvancedFilter Action:=xlFilterCopy, _
Set rngBuys = Range(RngBuyDestination, RngBuyDestination.End(xlDown).End(xlToRight))
rngBuys.Sort Key1:=RngBuyDestination(1, 1), Order1:=xlAscending, _
Key2:=RngBuyDestination(1, 2), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
'Copy Units to Units Remaining Column
rngBuys.Columns(rngBuys.Columns.Count).Offset(0, 2).PasteSpecial xlPasteFormulasAndNumberFormats
Set rngUnitsRemaining = Range(rngBuys(1, rngBuys.Columns.Count).Offset(0, 2), _
rngBuys(rngBuys.Rows.Count, rngBuys.Columns.Count).Offset(0, 2))
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Similar code for Sell has been implemented