Define Array by based on row 1 value instead of column number

DBR138 · Oct 23rd 2018

My current macro identifies the columns I want to output and the order in which to do so as follows:

Code

columnsToKeep = Array(1, 13, 36, 2, 3, 24, 8, 12)

I am running into problems because the source data is no longer going to be in the same order consistently. There will always be a heading in row 1 and the values will remain to accurate to the data below so I want to use that value but can't seem to make it work. The result would end up something like:

Code

columnsToKeep = Array(Item, Sequence, Sales Order Priority, Batch Number, Lot No, Firmed, Header/Line Status, Req Compl Date)

I realize that I will end up needing to define these but I am not sure how to put it all together.

Thank you!

jolivanes · Oct 23rd 2018

I don't know what you want to do once when you find the Column but here is an example.
Change references as required.

Code

Sub Maybe()
Dim columnsToKeep, i As Long, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
    columnsToKeep = Array("Item", "Sequence", "Sales Order Priority")    '<---- expand as required
        For i = LBound(columnsToKeep) To UBound(columnsToKeep)
            sh1.Cells(1, sh1.Rows(1).Find(columnsToKeep(i)).Column).EntireColumn.Copy sh2.Cells(1, sh2.Cells(1, sh2.Columns.Count).End(xlToLeft).Column).Offset(, 1)
        Next i
End Sub

DBR138 · Oct 24th 2018

I over simplified. I am defining a range for my source data an the specific columns I want to keep along with output order. Here is a better representation of the code before I go on to apply an advanced filter criteria:

Code

Dim wb As Workbook, wsSource As Worksheet, wsTarget As Worksheet
Dim columnsToKeep() As Variant, colm As Long, v, Destn As Range, CritRangeTopLeft As Range, CritRangeBottomRight As Range, CritRange As Range
Dim lastColumnSource As Long, lastRowSource As Long, filterRange As Range
[COLOR=#FF0000]Const filterField1 As Long = 8[/COLOR]
[COLOR=#FF0000]Const filterField2 As Long = 27[/COLOR]
Const criterion1 As String = "QC-Completed"
Const criterion2 As String = vbNullString


Set wb = ThisWorkbook
Set wsSource = wb.Worksheets("Data")
Set wsTarget = wb.Worksheets("Pass On")
On Error Resume Next: wsSource.ShowAllData: On Error GoTo 0
lastRowSource = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
lastColumnSource = wsSource.Range("A1").SpecialCells(xlCellTypeLastCell).Column
Set filterRange = wsSource.Range(wsSource.Cells(1, 1), wsSource.Cells(lastRowSource, lastColumnSource))
[COLOR=#FF0000]columnsToKeep = Array(1, 13, 36, 2, 3, 24, 8, 12) [/COLOR]   'define output columns to keep and their order
'this next section puts the headers on row 1 of the Pass On sheet:
colm = 0
For Each v In columnsToKeep
  colm = colm + 1
  wsTarget.Cells(1, colm).Value = wsSource.Cells(1, v)
Next v


Set Destn = Range(wsTarget.Cells(1, 1), wsTarget.Cells(1, colm))

Display More

So I will need to come up with a way to derive the column numbers used in the red lines above based on the column name in row 1 rather than assuming the columns will always be in the same order.

Define Array by based on row 1 value instead of column number

Participate now!

Share