Forum Discussion
modify VBA code to include dynamic columns instead of static
Do you and any colleagues that need to manipulate the results have Office 365 and can you set it to monthly updates? The array methods I have used have only come out in the latest releases of Excel and, though it would be possible to transfer the data using INDEX it would lack elegance.
Removing columns could be achieved by being more selective as to the columns brought through in the first place, even down to the level of bring key columns across individually within the downstream calculation.
Yes, we all have Office 365 ProPlus...with automatic updates.
I did some work last week to include filtering in the code....and it worked well! I did it with a latter part of the script though, not the first part that you've looked at. I'm not sure its what you envisioned...but it worked (just using the simple "sort" feature). Basically, not being a programmer, I simply recorded a macro with what and where I needed to filter and copied and pasted the rows between sheets. I then took the code from the macro and inserted into my main code. I needed to modify it a bit to always go down the the last row (since they will always be changing), but that worked well (even though you select a whole column while recording a macro it inserts a number for the last row). But, yeah, it worked.
Since it worked well toward the end of the code I'll be going back and doing that eventually for the first part of the code....because, as you mentioned, that first part of the code is quite time consuming. And my data sets will only get larger.
Unfortunately, the raw data from our instruments exports columns into different locations each time. Haytham Amairah wrote a nice bit of code (below) for me to move only columns that are included in the array to a new sheet. I'll likely change the code to run this first, then do sorting when the columns are static.
Greg
-----------------------
Sub MoveToUndilutedPLUS()
Application.ScreenUpdating = False
On Error Resume Next
Dim i As Integer
i = 0
Sheets("Undiluted").Activate
Dim columnNamesArray() As Variant
columnNamesArray = Array("Sample Type", "Sample Name", "Acquisition Date", "File Name", "Dilution Factor", "Analyte Peak Name", "Calculated Concentration (", "Analyte Concentration", "Accuracy")
Dim columnName As Variant
For Each columnName In columnNamesArray
Sheets("Undiluted").Activate
Dim columnNumber As Integer
columnNumber = Cells.Find(What:=columnName, After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
Cells(1, columnNumber).EntireColumn.Copy
Sheets("UndilutedPLUS").Activate
Sheets("UndilutedPLUS").Range("B1").Select
ActiveCell.Offset(0, i).Select
ActiveSheet.Paste
i = i + 1
Next columnName
Rather than searching for the column headers as text, I would recommend converting the raw data to a Table and selecting the columns you require by name. The formula I used to consolidate the columns into a single array that I could filter is a bit of a pig but it appears to work. I implemented it as a Named Formula 'SelectedData' that could then be filtered as before:
= CHOOSE( {1,2,3,4,5,6,7,8,9},
RawData[Sample Type],
RawData[Sample Name],
RawData[Acquisition Date],
RawData[File Name],
RawData[Dilution Factor],
RawData[Analyte Peak Name],
RawData[Calculated Concentration (ng/mL)],
RawData[Analyte Concentration (ng/mL)],
RawData[Accuracy (%)] )
I guess both the VBA and the formulae must present something of a problem for you to follow. I tend to lose or simply bewilder spreadsheet users when I describe the practice of addressing data by location (A1 or, in VBA, Cells(1,1)) as an abomination that has no place in serious model building. For me, the name is meaningful whilst the location is mere chance and will change as data is rearranged within the workbook. In your case, it simply doesn't matter in what order the columns are placed as long as they are named consistently.
Please note that most users would vehemently disagree with the underlined statement, they would see the A1 notation as integral to the concept of a spreadsheet.
This tackles the same problem but with Power Query.
Again, the columns are referenced by name so column shuffling or even moving the table to another sheet should make no difference.
Thanks so much again for helping me! Its really been great working on my code...and you were a great help starting out.
I have 2 questions about the code that you wrote for me.
1) is there a way to start the searching on Range "A1" instead of After:=Range("A1")? I guess it loops back to A1 at the end, but there are other columns that it will sometimes pick up on and consider a match before it gets back to column A. Right now, I've been coding in a line where is simply adds a column (i.e. to make column A into column B)...and that seems to work. Wondering if there is a better way.
2) more importantly, is there a way to have the loop NOT copy a column over if there isn't a match? Right now, for instance, the column "weight adj" might not be in a data set....yet, the code will choose the closest match (usually "Use record", since its closeby) and copy that to "Truncated Data". But, that can cause issues later on.
Is there a way, maybe, to set up a second loop to look for that term....and if is isn't there, then don't copy?
Thanks so much!
Greg
Sub TruncatedData() Application.ScreenUpdating = False On Error Resume Next Dim i As Integer i = 0 Sheets("Raw Data").Activate Columns("A:A").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Dim columnNamesArray() As Variant columnNamesArray = Array("Sample Type", "Sample Name", "Acquisition Date", "File Name", "Dilution Factor", "Analyte Peak Name", "Calculated Concentration (", "Analyte Concentration", "Accuracy", "Use Record", "weight adj") Dim columnName As Variant For Each columnName In columnNamesArray Sheets("Raw Data").Activate Dim columnNumber As Integer columnNumber = Cells.Find(What:=columnName, After:=Range("A1"), LookIn:=xlFormulas, _ Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Column Cells(1, columnNumber).EntireColumn.Copy Sheets("Truncated Data").Activate Sheets("Truncated Data").Range("B1").Select ActiveCell.Offset(0, i).Select ActiveSheet.Paste i = i + 1 Next columnName End sub