试着跟着潜水艇走。

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

字符串

尝试

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

字符串
但是请记住，如果没有找到匹配项，您将得到一个错误。

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

Debug.Print colnum

型
上面的代码显然不能处理工作表名称错误。

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

型
您也可以尝试.Evaluate作为一个单行程序解决方案
Application.Evaluate("=IFERROR(MATCH(""Reference"",Reference!1:1,0),""Not Found"")")个

PS：顺便说一句，我有点偏向.Find：）

完全是个菜鸟，但是你可能拼错了“column”，你写了“colnum”，如果这与此无关，请忽略。