CODEWITHSUNDEEP
excelvba
Andreas
Andreas

Reputation: 23968

Find column based on column header

I have this code and it only works if the header I'm looking for is in column B or "higher".
Lets say I have this table and need to find what column "Name" and "score" is in.

Name    score
John       1
Joe        5

If "Name" is in B1 and "score" is in C1 the following code will work: 

NameColumn = Split(Cells(1, Cells(1, 1).EntireRow.Find(What:="Name", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, searchdirection:=xlNext, MatchCase:=True).Column).Address(True, False), "$")(0)
ScoreColumn = Split(Cells(1, Cells(1, 1).EntireRow.Find(What:="score", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, searchdirection:=xlNext, MatchCase:=True).Column).Address(True, False), "$")(0)
                                                                ^^ <- search value

The above code would in the case return 

NameColumn = "B"
ScoreColumn = "C"

But suppose the columns are A & B then it will not find "Name" because it starts searching after cell 1,1 (A1) which is where the header is.

What can I change to make this work, or what alternatives are there to returning "A" and "B" in the example above?

Upvotes: 1

Views: 1552

Answers (2)

VBasic2008
VBasic2008

Reputation: 55073

Header Column Letter Calculation

You will have to add the After argument to the Find method pointing to the last cell .Cells(.Cells.Count) to start the search from the first cell .Cells(1). But as chris neilsen in the comments pointed out, this is not the reason your code would fail, because it would find Name at the end of the search.
Since you haven't defined what 'not working' actually means, and it is highly unlikely that you have misspelled Name in A1, I would assume that NameColumn returns an undesired result (<>A) meaning that you have used Name somewhere else in the first row and you really need to start the search from the first cell .Cells(1).

Short Version

Sub FindAfterShort()

    Dim NameColumn As String   ' Name Column Letter
    Dim ScoreColumn As String  ' Score Column Letter

    With Rows(1)
        NameColumn = Split(.Find("Name", .Cells(.Cells.Count), xlValues, _
                xlWhole).Address, "$")(1)
        ScoreColumn = Split(.Find("Score", .Cells(.Cells.Count), xlValues, _
                xlWhole).Address, "$")(1)
    End With

    Debug.Print "Column Letters '" & NameColumn & "' and '" & ScoreColumn & "'."

End Sub

Preferable Version

Sub FindAfterPref()

    Const cSheet As String = "Sheet1"   ' Worksheet Name

    Dim strName As String   ' Name Column Letter
    Dim strScore As String  ' Score Column Letter

    With ThisWorkbook.Worksheets(cSheet).Rows(1)
        strName = Split(.Find("Name", .Cells(.Cells.Count), xlValues, _
                xlWhole).Address, "$")(1)
        strScore = Split(.Find("Score", .Cells(.Cells.Count), xlValues, _
                xlWhole).Address, "$")(1)
    End With

    Debug.Print "Column Letters '" & strName & "' and '" & strScore & "'."

End Sub

A Small Study

Sub FindAfter()

    Const cSheet As String = "Sheet1"   ' Worksheet Name
    Const cFR As Long = 2               ' First Row
    ' The column where the Last Row Number will be calculated.
    Const cLRColumn As Variant = "A"    ' Last-Row Column Letter/Number

    Dim rngName As Range    ' Name Column Range, Name Range
    Dim rngScore As Range   ' Score Column Range, Score Range
    Dim lngName As Long     ' Name Column Number
    Dim lngScore As Long    ' Score Column Number
    Dim strName As String   ' Name Column Letter
    Dim strScore As String  ' Score Column Letter
    Dim lngLR As Long       ' Last Row Number (Calculated in Last-Row Column)

    With ThisWorkbook.Worksheets(cSheet).Rows(1)

        ' Find Method Arguments
        '   2. After: If you want to start the search from the first cell, you
        '             have to set the After parameter to the last cell. If you
        '             have the matching data in the first cell and you set the
        '             parameter to the first cell (default), it will still be
        '             found, but a little later (not mili, but micro seconds
        '             later) so it could be omitted.
        '   5. SearchOrder: Whenever a range is a one-row or a one-column range,
        '                   this argument can be omitted. Since you're searching
        '                   in a one-row range, "xlByRows" would have been the
        '                   more 'correct' way in this case.
        '   6. SearchDirection: This argument's parameter is by default "xlNext"
        '                       and can therefore be omitted
        '   7. MatchCase: This argument's parameter is by default "False". Since
        '                 I don't see the reason why you would have headers with
        '                 the same name, especially the ones you don't need
        '                 before the ones you need, it is omitted. If you really
        '                 need it, use "... xlWhole, , , True".
        Set rngName = .Find("Name", .Cells(.Cells.Count), xlValues, xlWhole)
        Set rngScore = .Find("Score", .Cells(.Cells.Count), xlValues, xlWhole)

        ' Address Arguments
        ' If the Address arguments are omitted, Range.Address returns the
        ' address as an absolute reference e.g. $A$1. When you split
        ' $A$1 you will get the following
        ' INDEX STRING
        '   0           - Empty string ("").
        '   1      A    - Use this i.e. index 1 for the split array index.
        '   2      1
        If Not rngName Is Nothing Then ' When "Name" was found.
            ' Calculate Name Column Number.
            lngName = rngName.Column
            ' Calculate Name Column Letter.
            strName = Split(rngName.Address, "$")(1)
        End If
        If Not rngScore Is Nothing Then ' When "Score" was found.
            ' Calculate Score Column Number.
            lngScore = rngScore.Column
            ' Calculate Score Column Letter.
            strScore = Split(rngScore.Address, "$")(1)
        End If

        Debug.Print "Column Numbers '" & lngName & "' and '" & lngScore & "'."
        Debug.Print "Column Letters '" & strName & "' and '" & strScore & "'."
        Debug.Print "Name Column Header Address '" & rngName.Address & "'."
        Debug.Print "Score Column Header Address '" & rngScore.Address & "'."

        With .Parent ' instead of "ThisWorkbook.Worksheets(cSheet)".

            '*******************************************************************
            ' This should demonstrate a case where you don't need the column
            ' letter (mostly you don't). You should use ".Cells", ".Range" is
            ' not an option.
            '*******************************************************************
            ' Column Number (lngName)
            ' Last Row Number calculated using Cells and lngName.
            If lngName <> 0 Then
                ' Calculate last row in Name Column.
                lngLR = .Cells(.Rows.Count, lngName).End(xlUp).Row
                ' Create a reference to the range from First Row to Last Row in
                ' Name Column.
                Set rngName = .Range(.Cells(cFR, lngName), _
                        .Cells(lngLR, lngName))
            End If

            '*******************************************************************
            ' This is the same as the previous and should demonstrate that
            ' when you already know the column letter, you have two choices:
            ' you can use ".Cells" or ".Range".
            '*******************************************************************
            ' Column Letter (strName)
            ' Last Row Number calculated using Cells and strName.
            If strName <> "" Then
                ' Calculate last row in Name Column.
                lngLR = .Cells(.Rows.Count, strName).End(xlUp).Row
                ' Create a reference to the range First Row to Last Row in
                ' Name Column.
                Set rngName = .Range(.Cells(cFR, strName), _
                        .Cells(lngLR, strName))
            End If
            ' Last Row Number calculated using Range and strName.
            If strName <> "" Then
                ' Calculate last row in Name Column.
                lngLR = .Range(strName & .Rows.Count).End(xlUp).Row
                ' Create a reference to the range from First Row to Last Row in
                ' Name Column.
                Set rngName = .Range(strName & cFR & ":" & strName & lngLR)
            End If
            '*******************************************************************

            ' Since the column letter is more user-friendly, the only use
            ' I can imagine where you might need it, is to inform the user e.g.
            MsgBox "Column '" & strName & "' contains the names and column '" _
                    & strScore & "' contains the scores.", vbInformation, _
                    "User Information"

        End With

        Debug.Print "Last (used) Row in Name Column '" & lngLR & "'."
        Debug.Print "Name Range Address '" & rngName.Address & "'."
        Debug.Print "Column '" & strName & "' contains the Names and column'" _
                & strScore & "' contains the scores."

    End With

End Sub

Upvotes: 0

IrwinAllen13
IrwinAllen13

Reputation: 577

Here is a quick UDF function that i have used in the past.

This may not be the best way to do it, but this is one I have used for many years.

Function ColumnHeaderLocation(entry As String, Optional ColumnNumber As Boolean)
Dim x, y As Long

y = ActiveSheet.Columns.Count
x = 1

Do Until x > y
    If ActiveSheet.Cells(1, x).Value = entry Then
        ColumnHeaderLocation = Split(ActiveSheet.Cells(1, x).Address(True, False), "$")(0)
        If ColumnNumber = True Then ColumnHeaderLocation = x
        Exit Function
    Else
        x = x + 1
    End If
Loop

End Function

Simply use the name of the column header (see example)...

NameColumn = ColumnHeaderLocation("Name") ' returns Column Location as a letter

Or

NameColumn = ColumnHeaderLocation("Name", True) ' Returns column Location as a Number

Upvotes: 1

Related Questions