how to find column header and return column number vba

user_12928857 0
Viewed 4599

i am looking for a specific column header in the first row of a sheet called "Reference" and wrote this:

colnum = Application.WorksheetFunction.Match("Reference", 1:1,0)

however, it is saying there is something wrong with my syntax. I am not sure what is wrong. Can someone correct this so that it returns the correct column number when it finds the header "Reference" in row one of my sheet.

vba excel
3 Answers

Have a try on following sub.

Sub FindHeader()
Dim sht As Worksheet
Dim Rng As Range

    Set sht = Worksheets("Sheet1") ' Define your desired sheet
    Set Rng = sht.Cells.Find("Reference") 'Find your desired value/text

        If Not Rng Is Nothing Then 'If found then do operation
            MsgBox Rng.Column 'You can do other actions here instead of messagebox
        Else
            MsgBox "Nothing found"
        End If

    Set sht = Nothing 'Clear memory
    Set Rng = Nothing

End Sub
user_55147470

Try

colnum = Application.WorksheetFunction.Match("Reference", _
         Worksheets("Reference").Rows(1), 0)

But remember you will get an error if no match is found. You will have to do proper error handling. For example

colnum = "Not Found"
On Error Resume Next
colnum = Application.WorksheetFunction.Match("Reference", _ 
         Worksheets("Reference").Rows(1), 0)
On Error GoTo 0

Debug.Print colnum

The above will obviously will not handle Sheet name errors. For that you can use something like

     On Error GoTo Whoa

     colnum = Application.WorksheetFunction.Match("Reference", _
              Worksheets("Reference").Rows(1), 0)

LetsContinue:
     Debug.Print colnum

     Exit Sub
Whoa:
     colnum = Err.Description
     Resume LetsContinue

You can also try .Evaluate for a one liner solution

Application.Evaluate("=IFERROR(MATCH(""Reference"",Reference!1:1,0),""Not Found"")")

PS: BTW I am slightly biased towards .Find :)

user_11405790

complete VBA noob here, but you might have misspelled 'column'? you wrote 'colnum' Please disregard if this has absolutely nothing to do with it

user_133218760
Related