Текст Range.Find() с возвратом каретки Excel VBA

Пожалуйста, попробуйте с кодом ниже

Public Function getColumn(headerText As String)
    str1 = Split(headerText, vbCrLf)
    str2 = UBound(str1)
    b = Range("1:1").Find(str1(0) & Chr(10) & str1(1)).Column
End Function

Вот в чем дело: текст с разрывом строки и без него НЕ является одним и тем же текстом, поэтому .Find не работает. Что вы должны сделать, это поиск шаблона. Я только что проверил это, и это работает, при условии, что если нет разрыва строки, должен быть пробел:

Sub test()
Dim rex As RegExp, ran As Range
Dim col As Integer, headerText As String

'read you headerText here

Set rex = New RegExp
rex.Pattern = RegexIt(headerText)

For Each ran In Range("1:1")
    If rex.test(ran.Text) Then
        col = ran.Column
        Exit For
    End If
Next ran

MsgBox col

End Sub

Function RegexIt(what As String) As String

what = Replace(what, "(", "\(")
what = Replace(what, ")", "\)")
what = Replace(what, "[", "\[")
what = Replace(what, "]", "\]")
what = Replace(what, "<", "\<")
what = Replace(what, ">", "\>")
what = Replace(what, " ", "[\n ]?")
what = Replace(what, vbCrLf, "[\n ]?")

End Function

Удачи!

Изменить: требуется ссылка на регулярные выражения Microsoft VBScript 5.5.

Edit2: отредактировано для использования переменных. Объяснение: Замените пробел в значении переменной на необязательный пробел/разрыв строки, экранирующие скобки для сопоставления с образцом.

Ваш код должен работать, даже если ячейка заголовка содержит возврат каретки:

Sub FindColumnWithTextInRowOne()
    Dim headerText As String, newCol As Long

    headerText = "whatever"
    newCol = Range("1:1").Find(headerText).Column
    MsgBox newCol
End Sub

Это связано с тем, что использование вами Find() не требует совпадения со ВСЕМ содержимым ячейки.

РЕДАКТИРОВАНИЕ №1:

Если ячейка заголовка была построена с использованием формулы, следует использовать несколько иной Find():

   Sub FindColumnWithTextInRowOne()
    Dim headerText As String, newCol As Long, r As Range
    headerText = Range("H1").Text
    newCol = Range("1:1").Find(What:=headerText, LookAt:=xlWhole, LookIn:=xlValues).Column
    MsgBox newCol
End Sub