VBA - Shared Value and Find Last End Point

  • Hi,

    Good day! I have a small project that needed to automatically make a calculation with respect the Qty and Dependent IDs.

    I already made a code that look on the dependent IDs but i was quite stuck about how to calculate the shared value until to the last point (NEID) and how to get the lasp EndPoint value.

    Here on the attachment you'll find my table with expected output.

    Logically, to start, look the empty value in column "FEID" and start the calculation from here the value in column "Value" should be divided with the sum value of column "Qty" and "DepID", that is 30/3 (for row 5) and 150/4 (for row 8), the result will be input in column ShValue which is 10 and 37.5 respectively. After getting this value, it will search the next NEID at column FEID, so in row 5, it has NEID of S005 which will search @ column FEID that is found in row 4, which is NEID S004, so it will compare first the value of ShValue (which is 10 taken previously @ row 5) and compare with the value @ column "Value" (20 @ row 4), which one is lowest will used to calculate the value @ colum ShValue, so 10/5, so you will get 2. It will process the same logic for the remaining NEID found until nothing found.

    For column "EndPoint", search only the last end point for each NEID, start with NEID S001 then get the FEID which is S002, this FEID will be used to go to next NEID with FEID until found the FEID empty. If FEID found empty get the last NEID so the last endpoint for NEID S001 is S005.

    Hope anybody could help me on this.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!