filtering OUT positions loop (Vba)

Copper Contributor

i assume i would need multiple loops but Im a newb lol 


highlighted are the pairs of offsetting positions (the different colors indicate the offsetting pairs)




how do i know they're offsetting? 




1) one has a"C" in column E, the other is BLANK


2) one is "B", the other is "S" in column D ("bought/sold" indicator)


3) they'll have OPPOSITE SIGNS (one positive, the other negative) in column A ("bought/sold AMT")


4) they'll have the same TAG code in column H 




the other challenge is that these offesets won't always be next to each other; one could be in row 2 and the other could be in row 100.


then I'd like the code to CUT and paste these offsetting positions to another sheet ..i want it to be CUT so that they're no longer in the original sheet as they'r

e cancelled positions


3 Replies


If i correctly understand there are offsetting positions which can be identified by an existing TAG code in column H. Each TAG code groups 2 or more positions which balance each other. These positions should be cleared from sheet1 and entered to sheet2. Then there are positions without a TAG code in column H which are still outstanding. These outstanding positions should remain in sheet1. For this scenario you can check if the code in the attached file does exactly what you are looking for.

correct, given that the matching TAG has the opposite signs of numbers in either column A or C


one has blank in column E,the other has "C" in the same column (ie column E)


The code in the attached file copies the pairs of offsetting positions with matching TAG code to sheet2 if there are opposite signs in column A and if there is either "C" or "" in column E. These positions are removed from sheet1.