Cannot Find Value with Carriage Return

[BrianExcel]

I am using the following line of code to search for the specific term "Percent WGKFailure". When I look at the value in the cell itself, there appears to be a carriage return or vbcrlf between "WGK" and "Failure", since it spans two lines.

I search for that specific term using the following:

    Set fnd = .Cells.Find("Percent WGKFailure", LookIn:=xlValues, LookAt:=xlWhole)

I have tried breaking that code up into multiple iterations, including "Percent WGK" & vbcrlf & "Failure", "Percent WGK" & vbnewline & "Failure", etc.

For some reason the code won't find that term. I would use just the "Percent WGK", but there is another column with that in it that can't be deleted.

Any idea how I can modify that particular line to look for the right combination?

 

[Michael M]

Have you tried

Set fnd = .Cells.Find("Percent" & Chr(10) & "WGKFailure", LookIn:=xlValues, LookAt:=xlWhole)

OR

Set fnd = .Cells.Find("Percent" & Chr(13) & "WGKFailure", LookIn:=xlValues, LookAt:=xlWhole)

You may also need to reinsert the leading trailling space in the string

 

[BrianExcel]

THanks Michael. Unfortunately that didn't work.

Also, just to be clear, the proper spacing would have been:

Set fnd = .Cells.Find("Percent WGK" & Chr(10) & "Failure", LookIn:=xlValues, LookAt:=xlWhole)

(The carriage return is between "WGK" and "Failure".

But even testing your theory with that didnt work. It still didn't find it. I am open to different ways to locate that cell, but there is even another cell with "Failure" in it, so I can't just search for that text and code based on it.

 

[MARK858]

On a copy of your sheet try deleting the Percent WGK part from the cell then see what =CODE(A1) returns after changing the A1 to the cell reference the Percent WGK was in.

 

[Michael M]

It's not a column width issue is it...rather than 2 different lines .....does making the column wider eliminate it ??
You could also remove the carriage return with this formula in a different column

=TRIM(SUBSTITUTE(A1,CHAR(10),"")) OR =TRIM(SUBSTITUTE(A1,CHAR(13),""))

 

[Fluff]

How about

Set Fnd = .Cells.Find("Percent WGK*Failure", , , xlWhole, , , False, , False)

 

[BrianExcel]

Still no luck. I guess I'll have to just delete that one manually. And MARK858, the character shows Chr(10) but I dont know why it just won't work.

 

[MARK858]

What about...

Set Fnd = .Cells.Find("Percent WGK*Failure*", , , xlWhole, , , False, , False)

 

[Fluff]

Something else that might be worth trying is
=CODE(MID(A1,8,1))

Does that come back with 32?

 

[BrianExcel]

I FINALLY found the problem. There were two spaces in between "Percent" and "WGK". Once I adjust the code for that and used the Chr(10) code it worked fine.

Thanks all!