multiple optionbuttons on userform to merge multiple sheets and calculate stock

[Mussa-A]

Hi guys
here my project is extemely complicated to calculate the stock across sheets by using userform
what I want :
1- when run the userform should be optionbutton(stock) default
and calculation the stock ( before calculation if there is duplicate item for each sheet alone should be merge for each duplicate item for each sheet alone based on column D except sheet FFR doesn't contains duplicate items at all ) and the formula based on sheets names for columns H,J should be( PURCHASE-SELLING +RETURNSS-RETURPP+FFR) based on column ID-CC
based on optionbutton (STOCK) take IDTR-100 as example

when merge in sheet PURCHASE= column (QTY)=90 & column (TOTAL) =10,200 as to sheet SELLING = column (QTY)=15 & column (TOTAL) =1,770 as to sheet RETURNSS column (QTY)=2 & column (TOTAL) =220 as to sheet RETURNPP column (QTY)=5 & column (TOTAL) =600 as to sheet FFR column (QTY)=200 & column (TOTAL) =24000
now the final result QTY=90-15+2-5+200=272(as show in listbox based on selected STOCK)
TOTAL=10,200-1,770+220-600+24,000=32,050(as show in listbox based on selected STOCK )
with considering there is minus values becuase there are some items are existed in some sheets but not all when implement the formula for calculation will give you minus
as to optionbutton PURCHASE


it includes three sheets when calculation (PURCHASE -RETURNPP+FFR)
take IDTR-102 as example
when merge in sheet PURCHASE= column (QTY)=5 & column (TOTAL) =500 as to sheet RETURNPP column (QTY)=4 & column (TOTAL) =400 as to sheet FFR column (QTY)=20 & column (TOTAL) =2000
the final result QTY=5-4+20=21(as show in listbox based on selected PURCHASE)
TOTAL=500-400+2000=2100 (as show in listbox based on selected PURCHASE)
as to optionbutton (SALES)

it includes two sheets when calculation (SELLING-RETURNSS)
take IDTR-110 as example
when merge in sheet SELLING = column (QTY)=0 & column (TOTAL) =0(there is not existed this item) as to sheet RETURNSS column (QTY)=10 & column (TOTAL) =3000
the final result QTY=0-10=-10(as show in listbox based on selected SALES)
TOTAL=0-3000=-3000 (as show in listbox based on selected SALES )
last thin should also after select one of them optionbutton when write the item based on column contain header ID-CC into textbox1 should filter in list box based on written into textbox1. and ignore columns CLIENT NO , INVOICE NO & PRICE when populate data in listbox .
note : each picture I did manually to understand my idea

if anybody interest to help me please inform me if need more details
thanks

[JohnTopley]

Please Login or Register  to view this content.

in main module1

Please Login or Register  to view this content.

[Mussa-A]

thanks . just question ? did you tested your code ,tested your attaching?
I no know where is my mistake. from the glance of your code yoy seem forgot procedure of listbox . I don't find it inside the code.
it doesn't show any thing in listbox when select any optionbutton. I hope to see this problem when you have free time .

[Mussa-A]

Hi John
did you see my replying ?

[Mussa-A]

another option guys ?

[JohnTopley]

I only focused on the "calculation" function as it wasn't clear to me what you wanted in the listbox for each selection.

Explain so others may/can help.

[Mussa-A]

Hi John
I attached some picture to see what I want because all of details are not enough that's why posted the pictures . the idea based on function to calculate for each specific sheets based on selected optionbuttons . the orginal code when run the useform show the data based on one sheet and search the item within the showd into listbox . now based on calculation of function when select one of the optionbuttons should show the data changes into listbox by calculation when select any optionbutton . how can I call your function and show into listbox when select one of the optionbutton to see the calculation in listbox,please?

[Mussa-A]

too strange !!!
I'm still waiting for experts to solve my questions.

[Mussa-A]

it seems to impossile to solve it .
posted here https://www.mrexcel.com/board/thread...stock.1208687/