Forum Discussion
modify VBA code to include dynamic columns instead of static
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
I think I solved my problems! Code below....
I realized that if the find function couldn't "find" the item, then it would error, but the "on error resume next" line would just go to the next line and copy the previously used column number. I changed the on error line to "on error goto jump:" where the "jump:" jumps to "next columnname" which skips copying the previous column.
I also just deleted the "After:=Range("A1")" term in the search parameters. I believe this then starts the search in A1....which is fine for what I need.
Greg
Sub TruncatedData() Application.ScreenUpdating = False On Error GoTo jump: Dim i As Integer i = 0 Sheets("Raw Data").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", "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, 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 jump: Next columnName Application.CutCopyMode = False Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub