I'm selling childrenbooks to a few primary schools and wanted some help in managing my inventory.
I've setup a spreadsheet and EE have helped in making some improvements but I'm struggling with the coding please can you help me with the VBA code (please see attachment).
1. In Worksheet A, I can't seem to change any of the column headings, each time I do a VBA error message comes up as "Run-time error '13', Type mismatch", not sure why this is, can the code be modified so that I can modify the column headings which will include inserting new columns and empty rows above the current headings?
2. In Worksheet A if I manually input into COlumn D a 'Yes' or 'No' then Column E and F should automatically update, however for some reason Column F remains empty, it is only when I re-enter 'Yes' or 'No' again does Column F update, could this be fixed so that Column F always updates when Column D is updated?
3. In Worksheet B, If I insert a column or row before or above cell A1, my macro stops working, is it possible to define the name of the references instead of using C:C or B:B hardcoded into VBA, so that if I format the worksheet to include additional columns or rows the macro will continue to work?
4. In Worksheet A, the VBA code for Column F tells me how many days have expired since I last updated Column D. However, the format at the moment, e.g., 5 days, doesn't allow me to perform any calculations on this field. I want Column F instead to return an integer (rounded down) and the format of the field to be a number so that I can perform calculations using the values in Column F
5. In Worksheet B, I want to introduce a new heading, an integer field, called 'No. of days of the Sale'. The code should work as follows when I select any random SaleID in cell C3 and a day from the drop-down menu in cell F3, the code should look at the SaleID in cell C3 and then go to Worksheet A Column C and find the the associated SaleID for example in row 25, and write, and permanently record, in Column I of the same row, the day the was selected in cell F3 from Worksheet B.
6. Similar to point 4 above, in Worksheet B I want to introduce a new heading called 'Price', again the code should work the same way as above. When I select any random SaleID in cell C3 and enter a price into cell G3, the code should look at the SaleID in cell C3 and then go to Worksheet A Column C and find the the associated SaleID for example in row 25, and write, and permanently record, in Column J of the same row, the price that was entered into cell G3 from Worksheet B.
7. In Worksheet A Column J I would like to calulate the number of days left of the sale, the calculation should looks like this Today's Date - [Column E + Column I], i.e., Today's Date - [Updated Date + No. of days of the sale]. The result should be an integer.
8. Lastly in Worksheet C I would like to show the results of filtering Column J of Worksheet A based on selecting from a drop-down in cell C2 of Worksheet C of how many days are left. Worksheet C should return from Worksheet A the list of SaleIDs, Date of Sale, Book and the Price.