CODEWITHSUNDEEP
vbaexcelfindcarriage-return
Alex Silverman
Alex Silverman

Reputation: 203

Range.Find() text with carriage return Excel VBA

What I'm trying to do
Locate the column whose header cell contains a unique string. In other words, I know the cell's text, and I know the cell is in row 1, but I don't know which column. NOTE: I want to search for the entire text, not just part of it. NOTE2: The text can vary, so I cannot hardcode the value into my code. Rather I need to use the variable in which the value is stored.

The problem
When there's no carriage return in the header text, a simple newCol = Range("1:1").Find(headerText).Column works fine. However, if there is a carriage return, this doesn't work. It throws up the error "Object variable or With block variable not set". Here's my exact header string:

Incomplete Email
(more text)

What I've already tried
I also tried using WorksheetFunction.Match(headerText, Range("1:1"), 0), but got the same issue.

Additional notes and requirements
This is part of an add-in, so I do not want to change anything in the user's excel sheet if I don't have to (i.e., I don't want to remove the carriage return).

Technically, I'm doing this in a function:

Public Function getColumn(headerText As Variant)
    getColumn = Range("1:1").Find(headerText).Column
End Function

Thanks!

Upvotes: 3

Views: 5036

Answers (3)

LocEngineer
LocEngineer

Reputation: 2917

Here's the thing: text with and without line break is NOT the same text hence the .Find fail. What you should do is a pattern lookup. I have just tested this and it works, provided that if there is no line break there shall be a space:

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

Good luck!

Edit: Reference to Microsoft VBScript Regular Expressions 5.5 required

Edit2: Edited for variable use. Explanation: Replace space in variable value with optionel space/line break, escape brackets for pattern matching.

Upvotes: 1

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

Your code should work even if the header cell contains carriage returns:

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

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

enter image description here


This is because your use of Find() does not require a match to the WHOLE contents of the cell.

EDIT#1:

If the header cell was constructed using a formula, then a slightly different Find() should be used:

   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

enter image description here

Upvotes: 0

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

pls try with below code

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

Upvotes: 1

Related Questions