Get column number by cell value in Excel

Question

I'm trying to get the column number of a cell in the first row with a specific value. The code I'm using isn't working for some reason.

Dim colNum As Integer
'sheetName is a String
With ActiveWorkbook.Sheets(sheetName)
colNum = column(.Match("ID", 1:1, 0))

It's telling me it's expecting a "list separator or )". How do I make it work?

Thanks

Answer 1

Try this code:

Dim colNum As Integer
'sheetName is a String
colNum = WorksheetFunction.Match("ID", ActiveWorkbook.Sheets(sheetname).Range("1:1"), 0)

Answer 2

Note that if the match is not found it will return an error; try this to handle that.

Dim Result As Variant
If Not VBA.IsError(Application.Match(...)) Then
    Result = Application.Match(...)
End If

This tries the match and if there is an error on the function it will not assign a result.

WorksheetFunction.Match Method (Excel)