Posts by gijsmo

Glad it worked for you.

Try adding the following code as one of the last steps in the macro:

Application.CutCopyMode = False

You're welcome, if you're having date issues (particularly in VBA) it is most likely a date has been interpreted as mm dd yy.

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,"")