CODEWITHSUNDEEP
vbaexcel
Monica
Monica

Reputation: 311

Find column number with merged cells vba

I would like to find the column with a specific header in a table where almost all the headers are merged. Here is an example with dummy data of what my table looks like:

Table with headers merged

I have tried either looking in row 1 and 2 (range A1:XFD1 and A2:XFD2), but it seems that vba cannot find the column I am looking for:

Sub getColumn()
Dim ColNum As Integer
On Error Resume Next
ColNum = Workbooks(ActiveWorkbook.Name).Worksheets("Data").Range("A1:XFD1").Find(What:="Unit Cost", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False).Column
MsgBox ("Column number is: " & ColNum)
End Sub

Thank you.

Upvotes: 1

Views: 1862

Answers (5)

user3598756
user3598756

Reputation: 29421

since Workbooks(ActiveWorkbook.Name) is the same as ActiveWorkbook and this latter is the default implicit workbook qualification, you can simply code the following helper function:

Function GetColumn(stringToFind As String) As Long
    On Error Resume Next
    GetColumn = Worksheets("Data").Rows("1:2").Find(What:=stringToFind, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False).Column
End Function

to be exploited in your "Main" sub as follows:

Sub main()
    Dim colNum As Long

    colNum = GetColumn("Unit Cost") '<--| returns 0 if "Unit Cost" is not found
End Sub

Upvotes: 1

user4039065
user4039065

Reputation:

Application.Match locates your unit cost column.

dim colm as variant
with activesheet
    colm = application.match("unit cost", .rows(1), 0)
    if not iserror(colm) then
        debug.print colm   'column as number
    else
        colm = application.match("unit cost", .rows(2), 0)
        if not iserror(colm) then
            debug.print colm   'column as number
        else
            debug.print "not found"
        end if
    end if
end if

Upvotes: 0

pokemon_Man
pokemon_Man

Reputation: 902

Just loop through your column and row

Sub findHeader()      
    For i = 1 To 5
       If Cells(1, i).Value = "Unit Cost" Then
          MsgBox Cells(1, i).Value
       End If
    Next i
End Sub

Upvotes: 0

Vityata
Vityata

Reputation: 43585

Can you try with this:

Option Explicit

    Sub getColumn()

        Dim ColNum      As Long
        Dim rCell       As Range
        Dim rRange      As Range

        On Error Resume Next

        Set rRange = Workbooks(ActiveWorkbook.Name).Worksheets("Data").Range("A1:XFD1")

        For Each rCell In rRange
            If rCell.MergeArea.Cells.Count > 1 Then
                msgBox (rCell.Column) 'consider debug.print rCell.column
            End If
        Next rCell

    End Sub

I have not tested it, but it should work... :) Pretty much you simply loop through your range and check each column whether it is merged. Then you give MsgBox.

Upvotes: 1

Alex
Alex

Reputation: 1642

You just need to change your 

.Worksheets("Data").Range("A1:XFD1")

to 

.Worksheets("Data").Range("A1:XFD2")

Since you're searching for 2 rows indeed

Upvotes: 2

Related Questions