Glad it worked for you.
Posts by gijsmo
-
-
-
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:
Code
Display MorePrivate 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:
Code
Display MoreDim 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:
Code
Display MorePrivate 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.
-
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.
-
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.
-
One way to process the input file into the output you have suggested is via a stand-alone macro as per attached:
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:
-
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:
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.
-