Posts by KjBox

    Probably easiest to do it with a formula, put this in E2 and copy down:


    =IF($D3="","",IF(ROW(A3)<3,IF(IF(D3<>"-",B3-$D3,B3)<0,0,IF(D3<>"-",B3-D3,B3)),IF(AND(A3<>A2,A3=A4),IF(IF(D3<>"-",B3-D3,B3)<0,0,IF(D3<>"-",B3-$D3,B3)),IF(AND(A3=A2,A3=A4),IF(OR(E2=C2,E2=0),0,IF(IF(D3<>"-",B3-D3,B3)<0,0,IF(D3<>"-",B3-D3,B3))),IF(IF(D3<>"-",B3-D3,B3)<0,0,IF(D3<>"-",B3-D3,B3))))))


    You will also need to make a small change to the VBA code.


    Change With Sheet1.Cells(1).CurrentRegion to With Sheet1.Cells(1).CurrentRegion.Resize(, 4)


    I am attaching the sample file with additional Items, Line Quantities and Gross Quantities so as to test for all possible scenarios. The Revised Line Quantity column has the above formula in it and the change to the code made, click the button as before.

    Just realised the code will fail if the Item in Column A is an Item with just a single row.


    Change the code to what is shown below to overcome this.

    OK I worked out the logic.


    Try the attached file, click the button on the sheet.


    Code assigned to the button:

    Try the attached


    Code assigned to the button

    Note this code needs to be run just once and can then be deleted. It will add the comments to all cells in the Table Column 9.

    It will be faster than your code, but even if it takes a few seconds, or even minutes, to run it does not really matter as it never needs to be run again.


    The code below is in the Sheet Object Module and will update the Column 9 comment for any change to Columns 23, 24 or 25

    Try the following


    First run this code with the sheet needing comments in Column I active

    The above code needs to be run just once to initially add required comments, if any, to Column I


    The code below will update the comment in Column I for any change in Columns W, X or Y in the same row.

    Note this code must be placed in the Sheet Object Module not a standard module

    Try this

    The error is because the sheet code name was different ("Foglio2" instead of "Foglio1"). The code below will work with any sheet code name, but the sheet that needs to have duplicates put into columns must be the active sheet.


    Note that the Table on the actual data example only allows for up to 4 duplicates so the previous error will occur if there are 5 duplicates.


    You're welcome.


    The reason for the error is that your Table allows for only up to 4 duplicates, add 3 columns to you table, "price-5", "from qty5" & "to qty5".


    Also the formatting for "price-5" needs to be added to the code


    Amended code