+ Reply to Thread
Page 2 of 2 FirstFirst 1 2
Results 201 to 274 of 274

problems with if, and, isnumber, find arguments in a formula

  1. #201
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    Hi Jason,

    I've just done a bit of googling and have managed to password protect all the coding so that it cannot be altered unless someone knows the password.
    I have also found a way of potentially protecting all sheets except the scan sheet so that no-one can mess with things and bugger up the book.
    Not sure on how to use the code though or where it would sit into the coding that is already there.
    What I found was the following:-

    “Me.Protect UserInterfaceOnly := True”

  2. #202
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    Quote Originally Posted by jason.b75 View Post
    I've added a quick test routine to the file here, if you type Archive into the scan field as a product barcode then the test will run through and change the font to a strikethough for anything that should be archived.
    Looks like a good starting point for the archiving

    Quote Originally Posted by jason.b75 View Post
    How did your user end up in the code? Unless something goes wrong and they click 'debug', they shouldn't end up in the code unintentionally.
    User error and not knowing what they are doing. This is why I wanted the whole thing as 'idiot proof' as possible for lack of better words.


    Quote Originally Posted by jason.b75 View Post
    The code can be password protected to restrict access while still allowing it to run. Not sure how that would work with onedrive as the sharing location though.
    I have managed to password protect all codes and seems to work ok with OneDrive.

  3. #203
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    Quote Originally Posted by shina67 View Post
    User error and not knowing what they are doing. This is why I wanted the whole thing as 'idiot proof' as possible for lack of better words.
    Whenever you think you've made something idiot proof, they upgrade the idiot!

    Protecting the sheets is simple enough, I was going to suggest that anyway, but was leaving that until last.

    The challenging part at the moment is getting the archive to do what is needed. Extracting the correct data from the audit sheets is fairly easy (as seen from the quick test that I've done in the last file), managing the workbooks that the data is being moved to is not so simple. I'll do a bit here and there when I get chance, but it's probably going to take the rest of the week at minimum.

  4. #204
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    Quote Originally Posted by jason.b75 View Post
    The challenging part at the moment is getting the archive to do what is needed. Extracting the correct data from the audit sheets is fairly easy (as seen from the quick test that I've done in the last file), managing the workbooks that the data is being moved to is not so simple. I'll do a bit here and there when I get chance, but it's probably going to take the rest of the week at minimum.
    I have found this on the net and hope that it will help
    Please Login or Register  to view this content.

  5. #205
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    If only it was that simple. Because we need to compare each of the audit sheets to the schedule before moving the data, a filter would not work.

    I can also see a couple of bits of bad coding that would bring back the dreaded file bloat that I eliminated earlier. (use of .EntireRow and .Offset without Intersect or .Resize).

    Thanks for trying to find something that might help though

    With the way we discussed structuring the archives, the code needs to:-
    -Look at each entry in the audit sheets.
    -Check if it has been removed from the schedule.
    -If it has been removed, check the completion date, otherwise skip to next entry.
    -Look for the correct worksheet to match the completion date with a filepath and worksheet range of something like (example, not proper syntax).
    \\Express Schedule Scanning\Archive\2019 Archive\May 2019.xlsx['Week 20'!A2]
    -Create the folder / file / worksheet if it doesn't exist.
    -Copy the entry from the audit to the next empty row in the archive.
    -Delete the original record from the audit.
    -Sort the archives into a sensible order.
    -Save all workbooks.
    -Close the archive workbooks.

    I might have missed a couple of steps, or may be overthinking some of it but that pretty much covers the structure of the process.

  6. #206
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    HI Jason,

    with the latest version there seems to be a problem with entering the data into the 'Input Source Data Here'.
    It doesn't self populate into the 'schedule' tab now and delete the data in the 'Input Source Data Here'.

  7. #207
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    Have you had any code errors?

    I haven't made any changes that would cause it to fail. The only possible reason that I can think of is that events are not running.

    Running the 'ToggleOn' macro will sort that out.

  8. #208
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    Hi Jason,

    The problem from post #206 is now resolved.

    I have another problem which I think we touched on earlier.
    The ability for the users to be able to print to different printers.
    I have found on google the following bit of code that looks like it might work for this option.
    Please Login or Register  to view this content.
    This is the dialogue that is describing the code from the website :-
    "There is a way, however, that you can have one-click printing of your worksheets on a designated printer. To do this, simply create a macro that changes the printer and then prints the worksheets. Here is a macro that will accomplish the task:
    When you create this macro on your system, make sure you change the printer name in the second line of the macro. It must exactly match the name of a printer on your system. (In this example the printer name is set to "HP LaserJet". You should change it to match the name of the printer you want used.)

    The trick is to create one of these macros for each of the printers you use. You can add a command for each printer to your Quick Access Toolbar so that each printer has its own print button. When you then click on the command or button, the appropriate macro is run and you get output on the desired printer."

    Obviously the last line of code would need changing as at the moment we ask how many copies are needed. I thought that this is something that could be added at the stage just before the amount required is asked for and ask which printer they want to print to ?

    Hope this is of help
    Last edited by shina67; 05-23-2019 at 10:13 AM.

  9. #209
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    I don't remember this coming up before, but it may have done. This thread is like a bowl of spaghetti

    Adding buttons to the worksheet or QAT is making more mess than you need. If you want the option to choose a printer each time then the simple way would be to force the print dialog window to open each time.

  10. #210
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    Hi Jason,

    I have sorted the printer issue.

  11. #211
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    I had already added something for the option to change the printer, so I've left it in place. It's only a single line in the Label_Print module, there is a comment in the code to say which line to delete if you want to stick with what you've already done instead.

    I've just finished the archive code, a quick test appears to work as expected. The code will open or create the folders / files / sheets as needed when it runs.
    A new folder (for each year) will be created for archives in the same folder that the scan workbook is located.
    A file will be created inside that folder for each month. Sheets will be added to the file for each week.

    The folders, files and worksheets will be created (if they don't already exist) based on the completion dates of the data being archived.

    The workbooks need to be open for the code to work but they will be saved and closed as the last part of the cycle.

    Please note that I've written the archiving code based on the scan workbook being stored locally. From what I have read, working with files stored on OneDrive will require the use of coding methods that I am not familiar with, or able to help you with.
    Attached Files Attached Files

  12. #212
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216
    Thanks Jason. I will start testing on Tuesday on my return to work. Hope you have a good bank holiday weekend .

  13. #213
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    Hi Jason,

    after a little test run everything seems to be working as it should.
    Could i ask when is the archive set to run as I cannot see in the coding where it is?

  14. #214
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    The archive is a manual run, either by scanning an *ARCHIVE* barcode in the product field, or running the code from the menu.

    I did say in an earlier reply that I thought trying to run it at a set time was a bad idea.

    If you want to try running it on a timer, then I'm sure that doing a search will provide some examples that you could use to call the 'Archive' marco when you want it to run.
    I have no faith in the reliability of using such methods, so I'll be sitting out on this one.

    I did try a before_close method, where the code checks to see how many people still have the shared file open. Archive runs when the last person closes it. But that also had too many failure points to be practical. Some of which are potential user errors that can not be idiot-proofed.

  15. #215
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216
    Quote Originally Posted by jason.b75 View Post
    The archive is a manual run, either by scanning an *ARCHIVE* barcode in the product field, or running the code from the menu.
    Oh ok now I can understand why I couldnt see anything relating to a time for it to run. I had assumed that was what you had done with you saying in an earlier post.

    Quote Originally Posted by jason.b75 View Post
    The workbooks need to be open for the code to work but they will be saved and closed as the last part of the cycle.

  16. #216
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    Hi Jason,

    The archiving seems to have worked a treat.
    The only issues I can with it is that the folder it created is called 'Archive 1899' instead of 'Archive 2019', the workbook created is called 'Archive' instead of the month and the sheet within the workbook is called 'Week' with no week # on it.

  17. #217
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    Has it created folders and files for 2019 as well?

    Does the Archive 1899 \ Archive \ Week (no #) sheet contain any data, or just headers?

    When I ran a test before posting the test file, I did encounter the same problem which was caused when the code was trying to archive the reprint sheet, which, at the time of running, contained no data.
    I did make an allowance in the code for this as the reprint stage is an optional part of the cycle, so having no data there is highly likely.
    I'm fairly sure that the code should allow for the cancelled stage audit to be empty as well, but will need to check that when I get home later.

  18. #218
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    I've just checked through the code, the scenario that you describe should be an impossible occurrence if the file is used correctly.

    The only possibilities that I can think of:-

    An anomoly caused by the way the way excel handles the file when it is shared over your network. In which case, I will not be able to fix it.

    Empty 'used' cells left in the sheet, which would be the result of manually deleting records from any of the audit sheets instead of letting the code do it.

    If the latter is the case, then that is simply a testing anomoly which should be eliminated once the file is protected.

    There is a macro in the 'ClearAll' module that is there purely for deleting the records from the audit sheets correctly during testing, although this will probably not 'fix' any sheets that have already been deleted manually.
    Last edited by jason.b75; 05-29-2019 at 06:53 AM.

  19. #219
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    It has created folders and files for 2019 which are all correct.
    The archive 1899 contains data also.
    The one i tested on is a local one and not shared.

  20. #220
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    One extra thought, did you copy the code to an older file for testing, or copy audit data from the old file to the new one?
    Previous versions did not add the completion date to the audit, so any records created by an older version could cause this to happen.

    Bottom line, the cause of the 1899 date is an empty cell in column K of the audit record being archived, we just need to establish why that is empty.
    Last edited by jason.b75; 05-29-2019 at 08:20 AM.

  21. #221
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216
    Quote Originally Posted by jason.b75 View Post
    One extra thought, did you copy the code to an older file for testing, or copy audit data from the old file to the new one?
    I copied audit data into the new one that you uploaded. I will try it again tomorrow with audit data that has the new column for completion date in it.
    Quote Originally Posted by jason.b75 View Post
    the cause of the 1899 date is an empty cell in column K of the audit record being archived, we just need to establish why that is empty
    Could this be as simple as the older data with no entry into column K. I will let you know if it still happens tomorrow when I only copy data over from the audits that has data in column k
    Last edited by shina67; 05-29-2019 at 12:39 PM.

  22. #222
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    Hi Jason,

    After some more testing it seems to work ok and generate the correct folder/workbooks/sheets if there is data in column K.
    I ran it with data in every cell and worked fine. When ran with data missing in column K is when it gave the 2 different output folders i.e. 2019 & 1899

    I've just been thrown a curve ball
    Is it easiest enough to add extra products into the schedule. i.e the raw data would have extra columns to include new products. At the moment I have been told that within the next month or so we are going to add 3 more products.
    Sorry about that it has come as a complete surprise to me.
    Last edited by shina67; 05-30-2019 at 05:52 AM.

  23. #223
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    It shouldn't affect the schedule, only the raw data conversion.

    I know for a fact that I defined specifics based on the number of products in the existing file in order to prevent data errors if anyone tries to paste in incorrect number of columns.

    I think that I should be able to make a slight change to cater for new products without too much difficulty.

    Beyond this, and any errors that you find is there anything other than sheet protection that I haven't done yet?

  24. #224
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    Quote Originally Posted by jason.b75 View Post
    It shouldn't affect the schedule, only the raw data conversion. I think that I should be able to make a slight change to cater for new products without too much difficulty.
    Brilliant there is always something that hasn't been thought of isn't there.

    Quote Originally Posted by jason.b75 View Post
    Beyond this, and any errors that you find is there anything other than sheet protection that I haven't done yet?
    Can't think of anything else. I pretty much think that everything is covered now that the archiving and print option is in place.
    Could you please put comments in on the coding so I can try and get my head around it a bit more than I have. This would help with me learning more about coding and trying in future to try and do more myself instead of asking for as much help as I have.

  25. #225
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    Quote Originally Posted by shina67 View Post
    Could you please put comments in on the coding
    If there was one question that I was really hoping you wouldn't ask

    Thinking a bit more about the extra products to be added later, are the headers of the other file that you copy the data from to create the schedule exactly the same as the currect headers in the 'Input source data here' sheet?

    Are those headers merged in the same way? i.e. Columns A to D, rows 1 and 2 merged. Row 2, columns E to T merged? (Not sure how critical all of it will be until I try a few things to see what works).

    To maintain the failsafe that I added previously that prevents incorrect data being pasted to the schedule, I'm thinking that the headers could be copied as well and used to validate the data.

    As long as the 'To Do' header is extended below any new products added, I think that I should be able to use that to validate the product columns.

  26. #226
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216
    Quote Originally Posted by jason.b75 View Post
    If there was one question that I was really hoping you wouldn't ask
    😂
    The additional products would be in the same format as it is now. They will be added in as extra columns. The rest of the formatting would be identical

  27. #227
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    No prob, it's going to take a bit of fiddling and a large dose of trial and error to get it to work the way that I'm thinking. Hopefully I'll get chance to get it done tomorrow.

    edit:-

    Something simple and obvious just hit me!

    Set the number of product permitted as a declared constant, then it's just a quick change to a single number in the code when products are added / discontinued.

    Would that work for you?
    Last edited by jason.b75; 05-30-2019 at 01:03 PM.

  28. #228
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    I realised after that my edited theory above wouldn't work.

    What I've done is revise the copy and paste method needed as I mentioned in post #225 (see Raw source data tab).

    Copy the data with the headers, then go to the Input data tab as normal. This time the sheet is completely blank and the headers will be pasted in as well (although you don't actually see this, it all happens out of view).

    The code looks at A1 to D1 and checks that they are all merged with the cell below. It also looks at E2 and checks that it is merged with other columns. This is the method used to validate the data that you are trying to paste. If it doesn't comply with this format then the code assumes that it is not valid for converting to the schedule.

    The number or different products in the source data is determined by the size of the merged cell E2. In the current file, the merged range is E2:T2, which is 16 columns wide, from this the code knows that there are 16 products to process.

    Hopefully I've done the changes correctly, time and testing will tell.

    Rather than me going through all of the code and trying to remember everything that I've done so that I can comment it, it might be easier if you pick out some sections that you don't understand and would like an explanation of.
    Attached Files Attached Files

  29. #229
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    Hi Jason,

    Everything seems to be working OK after some intense testing with multiple users using the workbook at the same time.
    The only problem there is but was expected is the archiving with it being on onedrive.
    I am manually copying/pasting the data from the onedrive workbook to a local copy and running the archive. Then copying/pasting the remaining data back into the workbook on onedrive.
    I have only done this twice so far but seems to get around the problem of the onedrive OK.
    I think that everything else is now sorted with it unless any errors/problems crop up when in full use.

    I thank you for all your time and hard work on this.

  30. #230
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    Quote Originally Posted by watsson View Post
    Please help me providing the solutions.
    If it is trying to archive from onedrive watsson. There is no easy solution as it is cloud based and not local. you cannot automatically generate a file/folder onto onedrive that i have been able to find out.

  31. #231
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    Quote Originally Posted by watsson View Post
    I have also faced the same problem.
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Please start a new thread - See Forum rule #4

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

  32. #232
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    Hi Jason,

    Everything seems good.
    I think the only thing left is sheet protection, as mentioned in post #223

  33. #233
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    That is something that you could have a go at

    This link explains the process in detail. Just set up the protection manually, then add the code as detailed in the link, probably the last example will be the most practical.

    https://www.ozgrid.com/VBA/excel-mac...cted-sheet.htm

    Note that you will need to save, close and re-open the workbook for it to take effect.

  34. #234
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    Hi Jason,

    I have applied the sheet protection with the use of the link that you attached in post #233. thanks for that.
    I have come to run archive this morning and it has come up with an error.
    I have attached a copy of the workbook and attached an image of the error.Capture.JPG

  35. #235
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    Just had a quick glance, I can't see the code from the link in the workbook, which means that the protection is still locking the sheets.
    You need to add one of the workbook open methods from the link, which one you use depends on how you have set up the protection. There is one for same password on every sheet and another for different passwords.

    I tried running the archive, but got a different error. The error that I encountered was definitely related to the protection still being active, your error could also be because of this.
    If you have applied protection to the archive then that is preventing the new sheet from being added. If that is the case then I think that adding and removing the protection programmatically could be difficult given that the files are being created on the fly.

  36. #236
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    Hi Jason,

    The password for protection that has been applied is 'express'.
    if that helps so you can read it.

  37. #237
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    Sorry, bit of confusion there. I could see the code in the workbook, but I couldn't find the code from the link anywhere in it.

    Been through it again and found it, but it was in the wrong place so it wasn't actually doing anything. The 'Workbook_Open' code needs to be in the workbook module, not the scan sheet module.
    While I was trying to figure out the issue, I also noticed that the code would incorrectly protect the sheets that shouldn't be protected (Scan and Source Data sheets), so I've made a slight change to resolve that.

    You need to remove the password code that you've added to the end of the Scan sheet module, and paste the new code below into 'ThisWorkbook' (Note that every open workbook will have a 'ThisWorkbook' module, so you will need to be sure that you have the correct one.
    Please Login or Register  to view this content.
    That now fixes the protection issues, and I'm seeing the same error that you had earlier. I'll need to look into it in more detail to find the cause, but I'm not going to get chance to do that today.

  38. #238
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    There was a June 2019 Archive file created when the code failed due to the password protection (this file contained no data, just an empty 'Sheet1'), deleting this file then running it again solved the problem.

    I'm going to do some more testing later, because I'm not 100% convinced that this is a true fix,the code should still have added the sheet. Also, when the archive ran successfully after deleting the empty file, I did notice that there was only 1 sheet in each of the files created.

    I'm fairly sure it ran ok with more than 1 sheet per archive before, but I deleted the earlier files prior to running it again to ensure that it was creating them correctly. If there is an issue with adding the extra sheets, then I'll need to look at it when I have more free time, possibly not until the weekend.

  39. #239
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    ok thanks Jason

  40. #240
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    I was just going to do a quick test while I had a few minutes to spare, and think that I have noticed an error in the schedule.

    Could you check that the dates in columns N to Q are correct in relation to the date in columns A and R. If they are wrong could you confirm what they should be (number of days before production completion date for each stage).

  41. #241
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    QC Inspection and Complete should be the same date as in column A. i.e. 11/6/19
    Crimped should be -1 day i.e. 10/6/19
    Routed should be -2 days i.e. 7/6/19
    Cut should be -3 days i.e. 6/6/19
    Taped should be -4 days i.e. 5/6/19
    This is obviously taking into account that we only work mon-fri.

  42. #242
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    Thanks, I thought it was that but couldn't find the original reference to it.
    Have a look at some entries in the schedule where the day of the completion date is between 1 and 12, the problem that I noticed was that the dates in such cases are still appearing in u.s. format, even though the code specified dd/mm/yyyy.
    Where this happens, the dates are adjusting back by months instead of days.

    I'll look into this a bit more and add a fix to it while I'm sorting the code error from the archive.

  43. #243
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    Hi Jason,

    I have ran the archive this morning and it has created folders for 1899, 2019 & 2020.
    I am assuming that this is due to the archiving coding?

  44. #244
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    Anything created by the archive coding should reflect the completion dates logges in the audit sheets.

    1899 indicates audit data with no completion date, which would mean that it was scanned in an older version of the file, or that the source data had a missing / invalid completion date.

  45. #245
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    In the audit sheets in the order complete date column it is putting in a date for 2020. It is also putting in dates in the future for 2019
    This is what is creating the 2020 archive folder.

  46. #246
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    Ok, I'll look into that. It's not something that I had noticed previously, it could be related to the other problem that I found, although it shouldn't be as the completion date is not changed by the code, only the other dates that are being calculated back from it.

    Does everything appear to have the wrong date, or only certain entries? If the latter, is there a common factor that stands out? For example, all entries with May dates are wrong, but everything else is correct?

    A common factor of the day being 12 or less could indicate a connection to the problem that I flagged in post #242.

  47. #247
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    would it help if i uploaded the workbook with the issue?

  48. #248
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    Hold off on that for now, I'll run some tests on the raw data that I have to see if it reproduces the problem.

    It's easier to identify issues with before and after data than with just the end result.

    A couple of known dates (original completion date from the pre-schedule source data) that have been archived incorrectly should help identify the cause.

  49. #249
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    ok I have a job with completion date of 05/06/2019 but has been archived and has a order completion date in column K of 16/01/2020.
    Another example is a completion date of 04/06/2019 but has been archived and has a order completion date in column K of 20/12/2019.

  50. #250
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    I couldn't recreate the issue that you were getting with the dates, but hopefully the changes that I've made now will solve the problem.

    Only done a quick test, but I think that all is ok.

    Please be aware that the changes that I've made will not 'repair' any existing entries with incorrect dates, so there may still be some residual errors if you try archiving any such records.

  51. #251
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    Hi Jason,

    Has this sorted the archiving and sheet protection problems. Just so I know when I'm testing what I'm looking out for.

  52. #252
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    As far as I could see from a quick test, it should all be sorted.

    Protection is set up so that the code has unrestricted access, but users will only be limited to the unlocked sheets only.

    The date issue that you encountered, I was not able to reproduce so you will need to watch for that.

    I found 2 issues with the archive, I believe that I have fixed both.
    Issue 1:- When a new archive workbook is created (first time given month is encountered), Week number was being ignored by the code, all data was recorded in the first sheet created regardless of date / week number.
    Issue 2:- When attempting to add more data to an existing archive workbook (month already encountered on a previous archive run) the code hits an error when trying to add a sheet for a new week.

    Both issues should be easy to test by scanning products for weeks 23 and 24 (first 2 weeks of June), archiving, then scanning products for weeks 25 and 26, then archiving again. Checking the archives after each run to ensure that the data has been assigned to the correct month and week.

    One thing that I have just thought of, there is nothing in the code to 'sort' the archived data, so it will appear in the order that it was copied over.

  53. #253
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    Quote Originally Posted by jason.b75 View Post
    One thing that I have just thought of, there is nothing in the code to 'sort' the archived data, so it will appear in the order that it was copied over.
    Cannot see this causing any problems as a simple filter and sort should sort this out

  54. #254
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    Hi Jason,

    I will know if the archiving is working ok on Monday morning as I have asked all users to log out for then so I can run the archive.
    Will let you know how it goes.

  55. #255
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    Hi Jason,

    Everything seemed to run OK with the archiving this morning.
    I have noticed another problem though.
    On the data I have inputted this morning into the schedule.
    It has inputted into the 'schedule' tab OK with the correct dates.
    The problem is when it comes to the audit sheets for all stages.
    The dates have gone weird. The date column has reverted back to the mm/dd/yyyy format. Also the order complete date column has reverted to '05-Jul-19' type date.
    I have attached a screenshot to help with the above explanation.Attachment 630412

    This appears to be only in the audit sheets.
    Last edited by shina67; 07-01-2019 at 08:25 AM.

  56. #256
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    Think I've sorted it, it was fine first test, but then I realised that I had used the old version of the file with the archive problems.

    I've just been through the correct version of the file and made the changes there but no time to run another test, hopefully all is working this time.

  57. #257
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    Hi Jason,

    just opened up the new file you have sent and noticed without doing anything some errors in the schedule.
    In a25, a26, a31, a42 etc. that the date formatting is incorrect. Is this what is causing the issues?

  58. #258
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    The data that is in the sheet is from a previous error that has been corrected, new data added to the schedule should be correct.

    Existing data will not be corrected by the changes, unless it is deleted and re-entered via the input sheet.

  59. #259
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    ok thanks Jason will test tomorrow when I enter new data.

  60. #260
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    Hi Jason,

    Obviously after some intense testing since my last post I would like to take the opportunity to thank you for all your help on this. Everything seems to be working exactly as it should. *fingers crossed*
    Thank you once again.

  61. #261
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    That is good to hear, the sound if silence had me thinking that you might be writing a big list of problems

    Given how long this thread has gone on for now, I think that it might be better to start a new one if you do have any additional requirements, if you do this though remember it will quite possibly be someone else helping you with it so you would need to provide enough information for them to work with.

    Also, you will need to flag the thread as solved by going to the 'Thread tools' link at the top of the page.

    Thanks for providing the challenge

  62. #262
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216
    Quote Originally Posted by jason.b75 View Post
    Thanks for providing the challenge
    No thank you for resolving the problems I Hadd along the way

  63. #263
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    Hi Jason,

    Sorry to trouble you again.

    This is just a quick one.
    If I wanted to change one of the stages i.e. change QCInspection to Wrapped.
    Would it be as easy as going into the coding and just changing the wording where it says QCInspection to the word Wrapped.
    The reason I ask is that is what I tried and it through out errors when trying to scan at stage Wrapped that there was nothing to Wrap even though all the stages before had been done.
    I also changed the word QCinspection to Wrapped in the stage varification tab and the tab that said qcinspection I changed to Wrapped.

  64. #264
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189
    Have you changed the column heading on the schedule tab? Also, from memory, audit tab name and possibly archive code as well. I'll check in detail when I get home later.

  65. #265
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    Quote Originally Posted by jason.b75 View Post
    Have you changed the column heading on the schedule tab?
    Not changed that. I will change it and try again.


    Quote Originally Posted by jason.b75 View Post
    Also, from memory, audit tab name and possibly archive code as well.
    Changed both them.

  66. #266
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    The column headings on the schedule tab shouldn't affect it, as the wording differs from the barcodes here, column numbers are used instead.

    The points that will need to be changed are the relevant Audit sheet name, the barcode on the Stage Barcodes sheet, along with the following lines of code. All must be identical (not case sensitive). You can see line numbers on the code window toolbar which show the position of the cursor within the code.

    In the Scan sheet module, line 104. (line 105, "Inspect" relates to the post scan pop-up message).
    In the Archive module, line 7
    In the ClearAll module, line 6
    In the OrderCancellation module, line 5
    In the Product module, line 4
    In the Quantity module, line 25
    In the Stage module, lines 35 and 76

    Hopefully, I didn't miss any.

  67. #267
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216
    Quote Originally Posted by jason.b75 View Post
    In the Stage module, lines 35 and 76
    Hopefully, I didn't miss any.
    Thanks Jason you got them all which is more than I did. I missed line 76 in the stage module

  68. #268
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,336

    Re: problems with if, and, isnumber, find arguments in a formula

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  69. #269
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    Hi Jason,

    After giving it some intense running live now there are just a few issues that I have picked up on.
    In the 'schedule' tab, cell d3 always reverts to 'product not found please scan a valid product barcode'
    If I wanted to take out the clear all that is in the workbook. i.e. when a job is completed through all stages, taped, cut, routed, crimped, complete & qcinspection.
    would it be as simple as deleting the coding that is in the modules labelled as 'ClearAll'.
    i realise that this will make the file larger but i could archive more regular.

  70. #270
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    I've not seen that issue with D3 unless an incorrect barcode is scanned, possibly this is related to some other changes that you're made, (I'm assuming that you mean Scan sheet, not Schedule).

    Not sure what you're referring to with the 'ClearAll' code, that doesn't relate to anything else. It was simply there as a way to wipe all data from the file while I was testing. Deleting that code will not affect on anything else.

    From my perspective, this thread has been resolved to a level way beyond that which would be considered normal for such a question, as such it should be flagged as solved.

    If you, or anyone else broke something after my final update, then it is now up to you, or them to fix it.

    The members of the forum are here to provide help and advice, not unlimited development support, there are people who earn a living form that.

  71. #271
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216
    Quote Originally Posted by jason.b75 View Post
    I've not seen that issue with D3 unless an incorrect barcode is scanned, possibly this is related to some other changes that you're made, (I'm assuming that you mean Scan sheet, not Schedule).
    No this is on the 'schedule' sheet

    Quote Originally Posted by jason.b75 View Post
    Not sure what you're referring to with the 'ClearAll' code, that doesn't relate to anything else. It was simply there as a way to wipe all data from the file while I was testing. Deleting that code will not affect on anything else.
    Sorry thought this code was to delete data from the schedule sheet when it had been scanned at all stages.. This is the code I am looking for within the book. I am wanting the data to stay in the schedule sheet even when scanned at all stages

    Sorry for troubling you again and I take on board your other comments in your last post. I will mark the thread as solved and leave you alone. Once again thank you for all your help with this project.

  72. #272
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    The D3 error is one that should be shown in the scan sheet if the incorrect barcode is scanned, I'll have a look at the weekend to see if a sheet reference is missing somewhere. If you haven't made any changes other than those in post #266 then that is the only cause that I can think of.

    While I'm at it, I'll take a look at the code that removes lines from the schedule once completed, if it only needs a couple of slight changes to the scan and audit processes then I'll post the changes for you, but I don't have the free time to spend hours on another major overhaul.

    This will be the absolute last change though!

  73. #273
    Forum Contributor
    Join Date
    01-13-2016
    Location
    Sheffield, England
    MS-Off Ver
    2016
    Posts
    216

    Re: problems with if, and, isnumber, find arguments in a formula

    Quote Originally Posted by jason.b75 View Post
    The D3 error is one that should be shown in the scan sheet if the incorrect barcode is scanned, I'll have a look at the weekend to see if a sheet reference is missing somewhere. If you haven't made any changes other than those in post #266 then that is the only cause that I can think of.
    Post #266 is the only changes that I have made.

    Quote Originally Posted by jason.b75 View Post
    While I'm at it, I'll take a look at the code that removes lines from the schedule once completed, if it only needs a couple of slight changes to the scan and audit processes then I'll post the changes for you.
    I thank you very much for this help.

  74. #274
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: problems with if, and, isnumber, find arguments in a formula

    I've been through the code twice and can't see any reason why the error message is posting on the schedule sheet, nor am I able to reproduce it, possibly this is a side effect of the workbook being shared. The only thing that I can suggest is to go through the code and qualify any references to D3 with a sheet name in the hope of curing it.

    Anywhere that you see
    Please Login or Register  to view this content.
    change it to
    Please Login or Register  to view this content.
    6 times in the Scan module, 3 in Product, 3 in Quantity, 8 in Stane and 2 in User.
    Anything else in these lines should remain unchanged. There is also 1 reference to D3 in the Archive module, which is already qualified with the scan sheet name, so doesn't need to be changed.

    To remove the schedule entry deletion from the final scan, the following lines need to be removed from the Scan module
    Please Login or Register  to view this content.
    These are near the end, lines 135 to 137.

    Then to add the removal to the Archive stage would just need a simple loop to run through column M in the schedule and delete any row where the value = 0.

    I seem to recall that you wanted to try and learn how to do some of this yourself, so here would be a good place to start. Use an older copy of the test file and see if you can get it working.
    Instead of trying to change the existing archive, just start with an empty module and try to write something to delete the rows from the schedule, I'll help you add it into the Archive later.

    Hint - it is better to start from the bottom and work up when deleting rows.

    I'll take a look tomorrow to see how far you've progressed

+ Reply to Thread
Page 2 of 2 FirstFirst 1 2

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Having trouble with =IF(ISNUMBER(SEARCH... formula to find keywords from a cell.
    By mmkessler in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-09-2016, 12:37 PM
  2. Replies: 4
    Last Post: 05-07-2012, 07:13 PM
  3. Tweak array formula IF(ISNUMBER(FIND...
    By Greed in forum Excel General
    Replies: 0
    Last Post: 11-22-2011, 11:42 AM
  4. Tweak array IF(ISNUMBER(FIND formula
    By Greed in forum Excel General
    Replies: 4
    Last Post: 10-25-2011, 12:59 PM
  5. Function arguments prompt causing problems
    By bryan641 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-12-2010, 08:55 AM
  6. isnumber find formula HELP please
    By billyboy630 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-10-2008, 03:14 PM
  7. Problems with IF, COUNTIF, arguments, etc.
    By Bemidji in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 02-13-2008, 06:57 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1