VBA code to deal with Shift+Enter

[ankita.sethi]

Hi,

i am writing a code to find a text in a particular row in excel. The problem is that the text exists with Shift+Enter in it twice (manual line break).

The text is "Ave Speed of Answer"; but entered like this:
Ave
Speed
of Answer

I have tried the following:
ASA = Range("A11:Z11").Find("AveSpeedof Answer", Range("A11"), xlValues, xlWhole, xlByColumns, xlNext).Column

Also:
ASA = Range("A11:Z11").Find("Ave" & Chr(11) & "Speed" & Chr(11) & "of Answer", Range("A11"), xlValues, xlWhole, xlByColumns, xlNext).Column

It gives the error "Object Variable or With Block Variable not set". In my experience, this error pops up when it cannot find the text.

Please help!!

 

[taurean]

Ankita,

Have you declared the variable "ASA" as range? This error will come if a variable is declared as object.

 

[ankita.sethi]

No. I have declared it as long.

 

[jkpieterse]

Alt+Enter seems to be chr(10).....

 

[taurean]

Yup, Jan is right. Take a look at the following snippet.

Public Sub Snippet()
Dim sString As String
Dim iColumn As Integer
sString = "Ave" & Chr(10) & "Speed" & Chr(10) & "of Answer"
iColumn = Cells.Find(What:=sString).Column
MsgBox iColumn
End Sub

 

[ankita.sethi]

Thanks a lot!
I cant believe I did that mistake
Apologies!!