Forum Discussion

Brass Contributor

Aug 05, 2019

modify VBA code to include dynamic columns instead of static

I have the following code which looks for the word "unknown" in column "4" (of sheet "Raw Data") and then copies the corresponding row of data to a different sheet ("undiluted"). However, sometimes t...

Macros and VBA

gms4b

Brass Contributor

Aug 07, 2019

@Haytham Amairah

So, I'm having a hard time now figuring out how to add a second loop(?) to do the same thing for the "Diluted" and "DilutedPLUS" sheets. I guess you'd have to add "j as an integer" at the beginning, but then I get lost after that, lol. I can't figure out where to trigger the second loop with the new sheet names.

Thanks,

Greg

(current code below)

----------------------------------------------------------------------

Private Sub CommandButton2_Click()

Application.ScreenUpdating = False

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", "Analyte Concentration", "Calculated Concentration (")

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

Sheets("Undiluted").Cells(1, columnNumber).EntireColumn.Copy

Sheets("UndilutedPLUS").Activate
Sheets("UndilutedPLUS").Range("A1").Select
ActiveCell.Offset(0, i).Select
ActiveSheet.Paste
i = i + 1
Next columnName

ActiveSheet.Range("A1").Activate
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Haytham Amairah

Silver Contributor

Aug 08, 2019

gms4b

It's easy, just copy the code in a new button in the Diluted sheet, and change the sheet names in it.

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False
    
    Dim i As Integer
    i = 0
    
    Sheets("Diluted").Activate
    Dim columnNamesArray() As Variant
    columnNamesArray = Array("Sample Type", "Rack Type")
    
    Dim columnName As Variant
    For Each columnName In columnNamesArray
     
    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("DilutedPLUS").Activate
    Sheets("DilutedPLUS").Range("A1").Select
    ActiveCell.Offset(0, i).Select
    ActiveSheet.Paste
    i = i + 1
    Next columnName

ActiveSheet.Range("A1").Activate
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

gms4b
Brass Contributor
Aug 08, 2019
Haytham Amairah

Yes, yes, lol. I was thinking the same thing after I left work yesterday. I'm sure there's a way to combine it together into some sort of loop, but this is easier. I also created a 4th command button which sequentially runs each of the first three scripts! In the end, I think it'll be nice to have the option to run things separately....or just run everything.

Thanks again!

Greg