chipchip
New Member
- Joined
- Aug 22, 2023
- Messages
- 2
- Office Version
- 365
- 2021
- 2019
- 2016
- 2013
- Platform
- Windows
- MacOS
- Mobile
- Web
I have a data sheet with 5 columns:Column A is the declaration number: assume there are 2 declaration numbers, 100 and 200.
Column B is the date: assume there are 2 corresponding dates, 08/22/2023 and 08/24/2023.
Column C is the code (identifier): assume the code is always NPL01.
Column D is the declaration amount: Corresponding to each declaration number, there are 1000 and 2000
.Column F is the product code: will be filled with the product code when the quantity is divided.
I also have an Allocation sheet with 5 columns:
Column A is the product code: assume there are 2 product codes, A100 and A200.
Column B is the quantity of products: corresponding quantities are 1000 and 2000.
Column C is the code (identifier): assume the code is always NPL01.
Column D is the standard: corresponding standards are 1.2 and 0.3.Column E is the requirement = quantity * standard.
Product Code Product Quantity Code Standard RequirementA100 1000 NPL01 1.2 1200A200 2000 NPL02 0.3 600
I want to use VBA code as follows:
If product code A100 requires 1200 using NPL01, then check if there are any declarations with code NPL,
if yes, determine the declaration amount.If the declaration amount = requirement, then use that declaration for product A100 and fill the product code in column F.
If the declaration amount > requirement, then multiply that declaration by 2. The original declaration amount will be 1200 corresponding to the requirement of product 100, and fill the product code in column F. The multiplied declaration amount will be the total old amount - 1200.
If the declaration amount > requirement, then use that declaration for product A100 and fill the product code in column F. Next, check if there are any declarations with code NPL01. If yes, check if the amount matches the remaining requirement. Then repeat the process as above, comparing the declaration amount with the remaining requirement.
Declaration Number Date Code Declaration Amount Product Code100; 08/22/2023;NPL01; 1000; A100 and 200; 08/24/2024; NPL01; 200; A100 and 200; 08/24/2024; NPL01; 600; A200 and 200; 08/24/2024; NPL01; 1200
This is the desired result for the given example. Please help i can solve this problem with vba code or any fastest possible way
Column B is the date: assume there are 2 corresponding dates, 08/22/2023 and 08/24/2023.
Column C is the code (identifier): assume the code is always NPL01.
Column D is the declaration amount: Corresponding to each declaration number, there are 1000 and 2000
.Column F is the product code: will be filled with the product code when the quantity is divided.
I also have an Allocation sheet with 5 columns:
Column A is the product code: assume there are 2 product codes, A100 and A200.
Column B is the quantity of products: corresponding quantities are 1000 and 2000.
Column C is the code (identifier): assume the code is always NPL01.
Column D is the standard: corresponding standards are 1.2 and 0.3.Column E is the requirement = quantity * standard.
Product Code Product Quantity Code Standard RequirementA100 1000 NPL01 1.2 1200A200 2000 NPL02 0.3 600
I want to use VBA code as follows:
If product code A100 requires 1200 using NPL01, then check if there are any declarations with code NPL,
if yes, determine the declaration amount.If the declaration amount = requirement, then use that declaration for product A100 and fill the product code in column F.
If the declaration amount > requirement, then multiply that declaration by 2. The original declaration amount will be 1200 corresponding to the requirement of product 100, and fill the product code in column F. The multiplied declaration amount will be the total old amount - 1200.
If the declaration amount > requirement, then use that declaration for product A100 and fill the product code in column F. Next, check if there are any declarations with code NPL01. If yes, check if the amount matches the remaining requirement. Then repeat the process as above, comparing the declaration amount with the remaining requirement.
Declaration Number Date Code Declaration Amount Product Code100; 08/22/2023;NPL01; 1000; A100 and 200; 08/24/2024; NPL01; 200; A100 and 200; 08/24/2024; NPL01; 600; A200 and 200; 08/24/2024; NPL01; 1200
This is the desired result for the given example. Please help i can solve this problem with vba code or any fastest possible way