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:

Private Sub CbDuration_Change()
If CbDuration.Value = "28 Days" Then
TxReview.Value = Format(DateAdd("d", 28, TxDate.Value), "dd mmm yy")
End If
If CbDuration.Value = "2 Months" Then
TxReview.Value = Format(DateAdd("m", 2, TxDate.Value), "dd mmm yy")
End If
If CbDuration.Value = "3 Months" Then
TxReview.Value = Format(DateAdd("m", 3, TxDate.Value), "dd mmm yy")
End If
TxReview_AfterUpdate
End Sub

You are welcome...all the best

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:

 Dim ColumnNumbers As Variant 
 
 ReDim ColumnNumbers(0 To 7)
 ColumnNumbers = Array(1, 2, 3, 4, 6, 9, 10, 11)
 
 Set rngDataRange = ActiveSheet.Cells(1, 1).CurrentRegion
 With rngDataRange
   Select Case .Columns.Count
   Case 11
   .RemoveDuplicates Columns:=(ColumnNumbers), Header:=xlYes 
  
   End Select
 End With

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

With no actual sample data to work with, it will be difficult to diagnose.

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:

Private Sub TxNewDoB_AfterUpdate()
  Dim dDate As Date
  Dim iAge As Integer
    
  On Error Resume Next
  
  TxNewDoB.Value = Format(TxNewDoB.Value, "d mmm yy")
  dDate = DateValue(TxNewDoB.Value)

  iAge = DateDiff("yyyy", dDate, Date)
      
  If DateSerial(Year(Date), Month(dDate), Day(dDate)) <= Date Then
    TxNewAge.Value = iAge
  Else
    TxNewAge.Value = iAge - 1
  End If

  On Error GoTo 0
  
  Select Case iAge
    Case 18 To 21
      CbNewRange.Value = "18-21"
    Case 22 To 29
      CbNewRange.Value = "22-29"
    Case 30 To 39
      CbNewRange.Value = "30-39"
    Case 40 To 49
      CbNewRange.Value = "40-49"
    Case 50 To 59
      CbNewRange.Value = "50-59"
    Case 60 To 69
      CbNewRange.Value = "60-69"
    Case Is > 69
      CbNewRange.Value = "70+"
    Case Else
      CbNewRange.Value = ""
  End Select
End Sub

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.

You are welcome!

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

Glad it worked for you, you're welcome !

I would normally use error handling as well however in this specific case it should not be required because the autofiltering was based on an array of unique values derived from the data in the column being filtered

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

The attached example should do what you want, based on the information provided.

There is a 2nd copy of Sheet1 - just to enable copying the data back for testing purposes.

DelRows v1.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:

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

or:

='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:

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

I have attached a generic file lister that may be more than you need but it will derive a list of video files and a bunch of related information like duration, aspect ratio, etc etc. It can also be used to list eg image files, audio files or just as a general file lister (depending on what you value select in the "Extra Data to Show" drop list).

It takes as input the folder that contains the files to be listed and the folder where you want to write the output file to. For video files, the output filename is in the format of Video List dd-mm-yy.xlsx

There is one small catch to this. It requires the MediaInfo DLL to extract the metadata from the files. More specifically, it requires the 32bit version of the DLL (the macro is only written for 32 bit version of Excel).

The 32 bit MediaInfo DLL for Windows can be obtained from here: https://mediaarea.net/en/MediaInfo/Download/Windows

Best to just grab the DLL file in zip format as you will need to place this file on the windows path somewhere (eg: C:\Windows\System32). Also, the file needs to be renamed to MediaInfo32.DLL to work with this macro (to avoid confusion in case you have the 64 bit version of the MediaInfo GUI installed).

If you want to place the file in another folder, you will need to modify the MediaInfoLibDLL class module and adjust all the declarations eg:

From:

Private Declare PtrSafe Function MediaInfo_New Lib "MediaInfo32.dll" () As LongPtr

To:

Private Declare PtrSafe Function MediaInfo_New Lib "C:\Temp\MediaInfo32.dll" () As LongPtr

If you're comfortable doing that, this will provide a decent list of file details.

There are a bunch of other options that you can play around with but hopefully most of them are self-evident.

ListFiles v2.5.xlsb

Thanks for letting me know, you're welcome

One way to do this is via a user defined function (UDF) using regular expressions (regex) as per attached example

Extract.xlsm