Jacque de Lacy
asked on
Excel VBA inventory spreadsheet requires some help with coding and referencing
Hi,
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.
Thanks
Muj
Book-sales.xlsm
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.
Thanks
Muj
Book-sales.xlsm
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
I tried adapting the last workbook, 'Book-sales.xlsm' by sktneer to my inventory workbook and can't figure out what is wrong with the code.
Could you please check and let me know what changes should have been made.
Thanks
Jacque
Book1.xlsm
I tried adapting the last workbook, 'Book-sales.xlsm' by sktneer to my inventory workbook and can't figure out what is wrong with the code.
Could you please check and let me know what changes should have been made.
Thanks
Jacque
Book1.xlsm
Please find the attached workbook with the tweaked code and see if it is working as per your requirement.
Book-sales-v2.xlsm
Book-sales-v2.xlsm
ASKER
Thanks for this, there appears to be a problem with the date format in Worksheet A, Column O, when you click on the check box in Worksheet 2 the date given is in mm/dd/yyyy format, whereas I would like it dd/mm/yyyy.
Also, would it be possible, for my education, if you would be so kind to broadly annotate sections of the VBA coding as to what it does in that part so that I can understand the logic you used?
Best Regards
Jacque
Also, would it be possible, for my education, if you would be so kind to broadly annotate sections of the VBA coding as to what it does in that part so that I can understand the logic you used?
Best Regards
Jacque
![Avatar of [ fanpages ]](https://filedb.experts-exchange.com/files/public/2020/2/28/4acbe344-900b-4e79-96ce-65fe58a6f004.png){kind=small}
Muj/Jacque (!)
Given you have not acknowledged my input, I will assume you are now progressing to conclusion with sktneer's comments/suggestion to address your (complete) requirements, so will stop monitoring the question.
sktneer: I doubt you will [:)], but if you need any assistance, please let me know.
Given you have not acknowledged my input, I will assume you are now progressing to conclusion with sktneer's comments/suggestion to address your (complete) requirements, so will stop monitoring the question.
sktneer: I doubt you will [:)], but if you need any assistance, please let me know.
Please find the attached workbook and see if the date issue is resolved.
Also I think you are smart enough to understand the code because you tweaked the code almost right except one mistake which you probably forgot to change otherwise you did pretty good job with that. Writing comments in all the code will require good time and I am very lazy in doing that. lol
But if you go through the code and pay some attention to the Ranges I have declared in all the three code, you will notice that I have tried to make the code completely dynamic so if you change the layout of your sheets, the code is supposed to work. What I have tried in the code that instead of hard coding the columns and rows in the code, I first found the position of headers on SheetA and if you read the variables names they are self explanatory.
One more suggestion is that instead of clicking the checkbox and allow the code to run automatically, just debug the code on Module1by pressing F8 key and there you will see each line of code in action, hold your mouse over the variables declared and notice their underlying values so you will know what a particular line of code is trying to achieve.
Book-sales-v3.xlsm
Also I think you are smart enough to understand the code because you tweaked the code almost right except one mistake which you probably forgot to change otherwise you did pretty good job with that. Writing comments in all the code will require good time and I am very lazy in doing that. lol
But if you go through the code and pay some attention to the Ranges I have declared in all the three code, you will notice that I have tried to make the code completely dynamic so if you change the layout of your sheets, the code is supposed to work. What I have tried in the code that instead of hard coding the columns and rows in the code, I first found the position of headers on SheetA and if you read the variables names they are self explanatory.
One more suggestion is that instead of clicking the checkbox and allow the code to run automatically, just debug the code on Module1by pressing F8 key and there you will see each line of code in action, hold your mouse over the variables declared and notice their underlying values so you will know what a particular line of code is trying to achieve.
Book-sales-v3.xlsm
@fanpages
You help is always welcomed. :)
You help is always welcomed. :)
ASKER
[ fanpages ] I'm very sorry, I'm new to Experts Exchange and didn't realise that I had offended, your advise was perfect and I thought that each expert was adding to the work. Sorry for this.
No problem about annotation I'll go through the code and work it out. Thanks
I tried to adapt the code to another spreadsheet as my first attempt failed but this time I have partially succeeded but can't seem to return a few of the columns again.
Can you see what I've done wrong?
Book_Sales.xlsm
No problem about annotation I'll go through the code and work it out. Thanks
I tried to adapt the code to another spreadsheet as my first attempt failed but this time I have partially succeeded but can't seem to return a few of the columns again.
Can you see what I've done wrong?
Book_Sales.xlsm
Not a problem, Jacque.
As long as you get the help you need, that's important.
Good luck with your project.
As long as you get the help you need, that's important.
Good luck with your project.
ASKER
Hi sktneer,
I tried to adapt the code to another spreadsheet as my first attempt failed but this time I have partially succeeded but can't seem to return a few of the columns again.
Can you see what I've done wrong?
Thanks a lot for all your help.
Book_Sales.xlsm
I tried to adapt the code to another spreadsheet as my first attempt failed but this time I have partially succeeded but can't seem to return a few of the columns again.
Can you see what I've done wrong?
Thanks a lot for all your help.
Book_Sales.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, you have been a great help.
Unfortunately I'm finding the workbook to be quite sensitive, sometimes Columns O and P are updating and sometimes not. I am having to close the workbook and restart it.
I am importing the code it to some much larger workbooks that contain data off the books I'm selling but to no avail and I don't know why and I believe I have changed everything.
I'm going to attach the actual workbook that I am importing into as a last resort to see if it can work.
If you could please modify the code to accommodate this workbook then I believe we are nearly there.
Also, I noticed when the update happens in the referenced cells, the formatting off the cells, e.g., line colour etc., disappears, is it possible to retain it?
Regards
Jacque
Auction-Catalogues-Inventory.xlsm
Unfortunately I'm finding the workbook to be quite sensitive, sometimes Columns O and P are updating and sometimes not. I am having to close the workbook and restart it.
I am importing the code it to some much larger workbooks that contain data off the books I'm selling but to no avail and I don't know why and I believe I have changed everything.
I'm going to attach the actual workbook that I am importing into as a last resort to see if it can work.
If you could please modify the code to accommodate this workbook then I believe we are nearly there.
Also, I noticed when the update happens in the referenced cells, the formatting off the cells, e.g., line colour etc., disappears, is it possible to retain it?
Regards
Jacque
Auction-Catalogues-Inventory.xlsm
Hi Jacque,
You started with one sample workbook and wanted the code (which you got from your previous question) to work in your sample workbook, I and fanpages offered you a solution. Then you implemented the code in a different workbook and I think that is fair enough considering you wanted to make sure that the code works in your new workbook also. Then again you applied the code to different workbook and requested to tweak the code to work in that case also and I did that. Now you are again trying to applying the code to a different workbook which as per your statement is your original workbook.
Is it fair for asking to tweak the same code once again as per your new workbook?
As you can see the last workbook I uploaded here with the code was version 4 and that means the code has been tweaked four times so far not because it was not working properly in one workbook but just because you implemented the code every time to a different workbook with different layouts.
Please remember that we all help here for free and assume you know this.
And I assume that I answered your question very clearly and tried my best to provide you an acceptable solution. So I request you to open a new question where you can provide the current code and a sample workbook which is exactly same as your original workbook and I am sure your new question will be answered as well.
Regards.
You started with one sample workbook and wanted the code (which you got from your previous question) to work in your sample workbook, I and fanpages offered you a solution. Then you implemented the code in a different workbook and I think that is fair enough considering you wanted to make sure that the code works in your new workbook also. Then again you applied the code to different workbook and requested to tweak the code to work in that case also and I did that. Now you are again trying to applying the code to a different workbook which as per your statement is your original workbook.
Is it fair for asking to tweak the same code once again as per your new workbook?
As you can see the last workbook I uploaded here with the code was version 4 and that means the code has been tweaked four times so far not because it was not working properly in one workbook but just because you implemented the code every time to a different workbook with different layouts.
Please remember that we all help here for free and assume you know this.
And I assume that I answered your question very clearly and tried my best to provide you an acceptable solution. So I request you to open a new question where you can provide the current code and a sample workbook which is exactly same as your original workbook and I am sure your new question will be answered as well.
Regards.
sktneer: Comments noted, but regardless of this, if you wish to request the question is re-opened, & the 100 points I received for 'assistance' are transferred to you so you gain the full benefit for your continued efforts with this thread, I will not object.
Well done for your perseverance.
Well done for your perseverance.
@fanpages
Actually I thought just opposite of that and thought you deserve more than 100 points for the time you have devoted to this question. Being a solution provider I understand the value of time and know it takes much more time and efforts (of course compromising with our own work) to deal with such type of complex requirements.
But unfortunately the OP didn't realize this point and as you can see I was expecting one last THANKS from the OP against what he has been offered by us.
That is what I hate the most specially when I devote time and my services to the free internet fora.
Anyways thanks for being so nice. Very much appreciated. :)
Actually I thought just opposite of that and thought you deserve more than 100 points for the time you have devoted to this question. Being a solution provider I understand the value of time and know it takes much more time and efforts (of course compromising with our own work) to deal with such type of complex requirements.
But unfortunately the OP didn't realize this point and as you can see I was expecting one last THANKS from the OP against what he has been offered by us.
That is what I hate the most specially when I devote time and my services to the free internet fora.
Anyways thanks for being so nice. Very much appreciated. :)
See if this is what you are trying to achieve.
Book-sales.xlsm