Assigning a variable to a column, based on header

[David2]

I'm searching for a macro that would go through headers (A1, B1, C1 ... P1), search for a text ("AAA" and "BBB") and then assign a variable to the column that contains the text (Set columnA=ColumnContaningTextAAAinHeaders and columnB=ColumnContaningTextBBBinHeaders)


I'm guessing I would first have to set a range for the headers, then loop through that and then assign a variable.

Any ideas?

 

[mumps]

Do you want to find the column number of the columns containing "AAA" and "BBB" and assign that number to a variable?

 

[David2]

Do you want to find the column number of the columns containing "AAA" and "BBB" and assign that number to a variable?

Aha, that works too. Just a clarification - some columns will contain AAA (their variable will be columnA) and some BBB (their variable will be columnB) but never both.

 

[mumps]

Is there more than one occurrence of AAA and BBB? If so, how do you want to name each variable?

 

[David2]

No, just one occurrence of each.

 

[mumps]

Try:

Sub David2()
    Dim columnA As Long, columnB As Long
    columnA = Rows(1).Find("AAA").Column
    columnB = Rows(1).Find("BBB").Column
End Sub

 

[David2]

Seems to be working great.

Two more things - what error handling should I have if AAA (or BBB) is not found? Currently I get "variable not set"?
And how can I delete all other columns except columnA and columnB?

 

[mumps]

Try:

Sub David2()
    Application.ScreenUpdating = False
    Dim lCol As Long, x As Long, columnA As Long, columnB As Long, AAA As Range, BBB As Range
    lCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    Set AAA = Rows(1).Find("AAA", LookIn:=xlValues, lookat:=xlWhole)
    If Not AAA Is Nothing Then
        columnA = AAA.Column
    Else
        MsgBox ("AAA not found.")
    End If
    Set BBB = Rows(1).Find("bbb", LookIn:=xlValues, lookat:=xlWhole)
    If Not BBB Is Nothing Then
        columnB = BBB.Column
    Else
        MsgBox ("BBB not found.")
    End If
    For x = lCol To 1 Step -1
        If x <> columnA And x <> columnB Then
            Columns(x).Delete
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

 

[David2]

One thing that I still don't understand - how do I reference the columns?

For instance how do I write this

Range("A1", Range("B" & Rows.Count).End(xlUp).Address).Sort Key1:=Range("A2")

using those variables?

Range((ColumnA & "1"), Range(ColumnB & Rows.Count).End(xlUp).Address).Sort Key1:=Range(ColumnA  & "2")

Doesn't seem to work -"Object variable or With block variable not set" error

 

[mumps]

"ColumnA" and "ColumnB" variables are the column numbers. "Range" does not accept column numbers to define a range. You have to use column letters. I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.