Transaction to Inventory excel VBA using macro

  • MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question


    Hello guys!

    I want to write a macro to receive data from the 'Transaction' sheet and gives me an 'Inventory' sheet but I don't know how to do it

    (the 'Transaction' sheet may contain many shares so the macro should count them all and if we sell a share that we don't have in the first place,(short it) it should warn us)

    could you please help me??

    ;(;(<3

  • mohi021

    Changed the title of the thread from “Transaction to Inventory VBA using macro” to “Transaction to Inventory excel VBA using macro”.
  • Try the attached.


    Click the button on the Transaction sheet.


    Code assigned to the button is:

    Note:


    You had an error in your sample file, the average Buy for Share 1 should be 7,752 not 7,749

    Any Short Sell will have the Inventory in Red text, I changed Share7 Sell to 4,000 to illustrate that.

    Files

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Thanks for posting links to cross-posting.


    Did you see my solution in Post#2?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Thank you very much, pal! that was so helpful;

    my one question is because my language is Persian (Arabic letters) and if I want to change the cells to Persian,

    would it require a special code? and would it destroy the code?

  • Not absolutely certain about that!


    I suggest you make a copy of the file first, then change that to Persian script.


    The attached file has the format for Inventory changed to include thousand separator.

    Files

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Thanks for posting links to cross-posting.


    Did you see my solution in Post#2?

    yes, pal. really helpful thank you <3,

    but from the code, I assumed that if I type the variables in Persian, the code won't work.

  • Again, not certain.


    I think you should try changing the sheet text to Persian and leaving the code variables in English.


    If that works then try changing the code too.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Again, not certain.


    I think you should try changing the sheet text to Persian and leaving the code variables in English.


    If that works then try changing the code too.

    Thank you very much.

    there are some words like 'buy' or 'sell' that are in the code and when I changed them to Persian(buy=خريد sell=فروش),

    they destroyed the code, but tickers work very nice.

    any idea pal?

  • In the code line 17 is If x(ii, 2) = "Buy" Then , try changing "Buy" to "خرید" but still within the quotation marks

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Sorry code line 19 not 17

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Please put a post in ExcelForum and MrExcel to say that the problem was solved at Ozgrid. Then people will not waste their time working on a solution for you.


    Thanks.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • You're welcome

    Hi Mr.Charles,

    I'm trying to understand the code and figure out which part is for which function.. but its a little bit complicated for me,

    could you please give me some ropes about it??

    thank you

  • Here is the code with comments added, I hope it will help you follow and understand what each part does!

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Here is the code with comments added, I hope it will help you follow and understand what each part does!

    Thank you <3