Excel VBA inventory spreadsheet requires some help with coding and referencing


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.

Jacque de LacyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi Muj,

To avoid a particularly lengthy (&, potentially, difficult to follow reply), I would suggesting splitting replies addressing subsequent points.  This not only helps readability, but also allows more than one "Expert" to participate.

I will start with points 1 to 3 (inclusive).  Other "Experts" can follow, addressing the same, or subsequent points, if they wish to.

1. Sorry, I don't seem to be able to reproduce the error in point 1.  Please can you relay an example cell you changed (& the value you changed it from/to)?

2. The Worksheet_Change() event code for [Worksheet A] only updates column [F], if there is a value in column [E].  To force both columns [E] & [F] to update together, please change the code as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D:D")) Is Nothing Then
    Cells(Target.Row, "E") = Format(Now, "dd/mm/yyyy hh:mm")                            ' *** MOVED FROM BELOW
'   If Cells(Target.Row, "E") <> "" Then                                                ' *** REMOVED
        Cells(Target.Row, "F") = DateDiff("d", Cells(Target.Row, "E"), Now) & " days"
'   End If                                                                              ' *** REMOVED
'   Cells(Target.Row, "E") = Format(Now, "dd/mm/yyyy hh:mm")                            ' *** MOVED ABOVE PREVIOUS STATEMENT
End If
End Sub

Open in new window

3. The "macro" you refer to, I think, is the FindSaleID() subroutine within the "Module1" code module.  That explicitly refers to cell [C3] within [Worksheet B]:

Set rngFound = Sheets("Worksheet A").Range("C:C").Find(What:=Sheets("Worksheet B").Range("C3"), LookIn:=xlValues)

If you name that cell to, say, FIND_SALEID, then the code could be changed as follows:

Sub FindSaleID()
Dim rngFound As Range

'Set rngFound = Sheets("Worksheet A").Range("C:C").Find(What:=Sheets("Worksheet B").Range("C3"), LookIn:=xlValues)              ' *** REMOVED
Set rngFound = Sheets("Worksheet A").Range("C:C").Find(What:=Sheets("Worksheet B").Range("FIND_SALEID"), LookIn:=xlValues)      ' *** ADDED
If Not rngFound Is Nothing Then
    If ActiveSheet.Shapes("Check Box 1").ControlFormat.Value = xlOn Then
        Sheets("Worksheet A").Cells(rngFound.Row, "D") = "Yes"
        Sheets("Worksheet A").Cells(rngFound.Row, "D") = "No"
    End If
    MsgBox "SaleID not found"
    Exit Sub
End If

End Sub

Open in new window

The attached workbook contains the changes mentioned above.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Here is my attempt but doesn't include the code for request 8.
See if this is what you are trying to achieve.
Jacque de LacyAuthor Commented:

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.

IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please find the attached workbook with the tweaked code and see if it is working as per your requirement.
Jacque de LacyAuthor Commented:
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
[ fanpages ]IT Services ConsultantCommented:
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.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:

You help is always welcomed. :)
Jacque de LacyAuthor Commented:
[ 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?
[ fanpages ]IT Services ConsultantCommented:
Not a problem, Jacque.

As long as you get the help you need, that's important.

Good luck with your project.
Jacque de LacyAuthor Commented:
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.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You didn't reference the correct sheet in one of the Find Range of the code and days are added with the no. of days as custom formatting so it shows days with the no. but the underlying value is still an integer. If you input 7 days in a cell, that will be a text string not a number so it cannot be used in any mathematical operations but if you input 7 in a cell and custom format it to show 7 days instead of only 7, the underlying cell value will still be 7 i.e. a real number and cell content will not be treated as a text string and hence can be used in mathematical operations.

On Data Tables, I have added custom formatting to those numbers and added the same custom formatting to the drop down also.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jacque de LacyAuthor Commented:
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?

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.

[ fanpages ]IT Services ConsultantCommented:
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.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:

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. :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.