Anthon
11-09-2016, 07:20 AM
Hi Guys,
Wonder if you can help me with this problem by writing a VBA Code. I have uploaded some Excel files for you reference.
1) VBA Code is to be written in the file "Recon".
1) Each period I get a data file titled "Master File". In that file there are 3 groups of data, namely Production, Waste and Sold. All items under Production has Numbers ending with ".8", Waste with ".2" and Sold with ".5". I'm only concerned with Production and Sold. In each group, I am only concerned with the category Export, so the VBA code should filter out any data categorized as Domestic and exclude the group called Waste. Any difference computed between Actual Value and Correct Value are highlighted in the file that I get, but I would like VBA to exclude differences less than 10 and more than -10 i.e. -10<Difference<10 should be rejected. For the rest of the Difference, the corresponding Number is to be retrieved by VBA.
2) Each Number retrieved from the Master File has its own Excel file identically named. In this case I have uploaded 2 files called 100.8.xlsx and 111.5.xlsx. I would like VBA to open each of these files and identify which line items do not belong within the spreadsheet. For eg, in 100.8, "Master File" Description indicates "Wood". In the file 100.8.xlsx, there is a list of different types of Wood. The items that are not part of the Wood category are Crystal Glass and Crystal Pure. The easiest way to filter these out is by using the Address column. You will notice that Address is stated as 342 for Wood and 189 for Glass regardless of the type of variant for each material.
3) Once the line items are identified, VBA should fill the "Recon" file. Under the column called Number, the corresponding Number from the Master File indicated there. If there are 2 line items in 100.8.xlsx that do not belong (in this case 2 Crystal), then there should be 2 rows in Recon. The Description in Recon should then state "RECON FOR CRYSTAL" while the Value column should always be a negative of the Value in the Number file (which is 100.8.xlsx in this case). The same example is repeated for 111.5.
Hope this is clear to you. Let me know if you still do not understand.
Wonder if you can help me with this problem by writing a VBA Code. I have uploaded some Excel files for you reference.
1) VBA Code is to be written in the file "Recon".
1) Each period I get a data file titled "Master File". In that file there are 3 groups of data, namely Production, Waste and Sold. All items under Production has Numbers ending with ".8", Waste with ".2" and Sold with ".5". I'm only concerned with Production and Sold. In each group, I am only concerned with the category Export, so the VBA code should filter out any data categorized as Domestic and exclude the group called Waste. Any difference computed between Actual Value and Correct Value are highlighted in the file that I get, but I would like VBA to exclude differences less than 10 and more than -10 i.e. -10<Difference<10 should be rejected. For the rest of the Difference, the corresponding Number is to be retrieved by VBA.
2) Each Number retrieved from the Master File has its own Excel file identically named. In this case I have uploaded 2 files called 100.8.xlsx and 111.5.xlsx. I would like VBA to open each of these files and identify which line items do not belong within the spreadsheet. For eg, in 100.8, "Master File" Description indicates "Wood". In the file 100.8.xlsx, there is a list of different types of Wood. The items that are not part of the Wood category are Crystal Glass and Crystal Pure. The easiest way to filter these out is by using the Address column. You will notice that Address is stated as 342 for Wood and 189 for Glass regardless of the type of variant for each material.
3) Once the line items are identified, VBA should fill the "Recon" file. Under the column called Number, the corresponding Number from the Master File indicated there. If there are 2 line items in 100.8.xlsx that do not belong (in this case 2 Crystal), then there should be 2 rows in Recon. The Description in Recon should then state "RECON FOR CRYSTAL" while the Value column should always be a negative of the Value in the Number file (which is 100.8.xlsx in this case). The same example is repeated for 111.5.
Hope this is clear to you. Let me know if you still do not understand.