Use VBA to implement a simple reconciliation system (goods statement for suppliers)

Project Background: there will always be suppliers in the factory. When establishing the business, they will send quotations of various material models in advance. According to the time sequence, the prices of the same materials will be updated in the subsequent quotations. If the quotation is confirmed by the factory, it will be delivered according to the order of the factory. The factory is responsible for checking the quantity of materials and registering and creating a book. At the end of the month, the supplier will send a statement of the current month's supply for the factory to confirm, this ticket contains the delivery date, model, quantity, price, total price and other details, the factory needs to verify one by one, send back the confirmation letter after the confirmation is correct, so that the confirmed quotation will prevail at the time of checkout.

This is the entire reconciliation process. The materials and quantity have been checked during the warehouse receiving. Therefore, the reconciliation is actually an error in the unit price and the total amount calculated on the unit price, when there are a large number of Model Materials and many orders, and quotations are constantly updated, the reconciliation at the end of the month still requires a lot of manpower. considering this, I think of a simple reconciliation system.

This reconciliation system is very simple, that is, first create a table to enter the supplier's quote in the order of recent and distant in the Excel table, take the table name as the quote, and then create another table statement, transplant the model and quantity of materials created in the warehouse, and then associate the quotation sheet with the statement. Write a VBA applet to implement the following functions, from top to bottom to find the material model, after finding the corresponding unit price, you can enter the corresponding unit price in the statement. As for how to get the amount, you can use the formula directly in the quote.

The following is a VBA Applet:

Sub a supplier can close the ticket to obtain the latest unit price () for irow = 5 to 39 Step 1 for J = 2 to 10000 Step 1 If worksheets (" "). range ("A" & J) = "" Then exit for end if range ("B" & irow ). TEXT = worksheets ("quote "). range ("A" & J ). text then range ("e" & irow) = worksheets (" "). range ("B" & J ). value end if next jnext irowend sub

Add a button to the current reconciliation table during running and specify the macro name. Note that the unit price entered in the statement starts from E5.