Posts by gijsmo

    Excel has a habit of interpreting dates as mm dd yy (even if that is not your local date setting).

    You generally need to code around it, maybe try:


    When using an array with RemoveDuplicates, it needs to be a zero based integer array.


    In the example file you posted there are only 11 columns, so the code snippet for this example would be:



    Obviously, you can expand this if your actual workbook has more columns but the principal will be the same.

    Can you please clarify.


    When you say "5 cell data's" does this relate to the number of cells being scraped from each PreTerm sheet or do you mean the issue occurs after 5 workbooks?


    If it relates to cells, the next step would be to try and work out if there is a particular cell on a particular sheet that may be causing the issue.


    It is very hard to diagnose without actual data/workbooks but obviously you cannot upload these if they contain sensitive data.

    One way to do this within the same block of code is as follows:


    Without 200 or so test files, it's difficult to diagnose the issue.


    Have you tried breaking the files up into groups of, say, 20-50 files and test on those ie, not all the files at once but eventually run through all files.


    This might help determine if it's the number of files being processed or if there is one or more files that may be causing the issue.

    One way that might make it easier to adjust the formulas is by changing the date parameters eg, for Jan 21:


    =COUNTIFS('2021'!$A:$A,"=Chemical",'2021'!$B:$B,">="&DATE(2021,1,1),'2021'!$B:$B,"<"&DATE(2021,2,1),'2021'!$H:$H,"=Buffer")


    This way, you only need to change the year value.

    I went back to basics and just modified my original code to also prompt for the Year as input, defaulting to the current year.

    This allows the user to select a different year as required and will also just add '2000' to the year value entered for the current century eg '22' entered will be interpreted as '2022'. Otherwise they can simply press enter and accept the default year value.


    ExportToCSV v2.xlsm

    As I indicated in my earlier example, the code worked with the original data provided.


    No error handling should be necessary because the SpecialCells in this case was based on the unique values in Col G in the original sample.


    The new data you provided however uses Col F instead of G and it includes 'unformatted' data such as "1.46505E+14" which needs special handling.


    The change from Col G to Col F and the 'unformatted' values now included in Col F should be able to be handled by the attached revised example. This currently works on the sample provided, there are on guarantees if this data is not representative of the actual data or if, for example, the data format changes.


    DelRows v2.xlsm

    One way to process the input file into the output you have suggested is via a stand-alone macro as per attached:

    InputExtractor v1.xlsm


    This will process all the sheets in the specified input file (based on the sample provided) and write the results to the specified output file.

    The "template" used for output file is stored as the Output sheet in this stand-alone macro.


    Based on the sample provided, the output file looks as per attached:

    Output.xlsx

    What do you expect the "File used" value to look like (on the Output sheet) when there are multiple files making up the formula eg:


    Code
    ='E:\DownloadedFiles\[Dummy2.xlsx]dummy1'!$B$6+'E:\DownloadedFiles\[Dummy2.xlsx]dummy1'!$C$5

    or:

    Code
    ='E:\DownloadedFiles\[Dummy4.xlsx]dummy 3'!$D$9+'E:\DownloadedFiles\[Dummy4.xlsx]dummy 3'!$D$9+'E:\DownloadedFiles\[Dummy3.xlsx]dummy2'!$D$9


    In the 1st example, the 2 files referenced happen to be the same but in the 2nd example there are 3 files in the formula and 2 of those are unique.

    Not sure if you're looking for a VBA solution but this scenario can be handled by a formula in column C

    eg, the formula for Cell C3 would be:

    Code
    =IF(AND(UPPER(LEFT(H3,3))="NET",D3>0),1009992,"")