Excel 2007 vba range.find method can't locate cell containing: 8" (2 chars long, pronounced: 8 inches)

Question

In a particular 1-column-wide range in a spreadsheet I need to use Excel 2007 VBA's range.find method to locate a text-valued cell containing the 2-character long value: 8" (pronounced in the US as Eight Inches). The .find method is in a sub that works fine for all other searches it's doing, but it cannot seem to find 8", or in fact any text value with a trailing double-quotation mark.

In the code below, initially sComparisonText contains 8"

I've tried adding from 1 to 6 double-quotation marks to the end of sComparisonText, using Chr(34), but the .find method still returns Nothing.

Various searches have noted the Chr(34) approach, and also stacking double-quotation marks: """" resolves to ", """""" resolves to "", etc. I've also looked into the .find method having a specialty escape character, but no success there either.

If Right(sComparisonText, 1) = """" Then
    sComparisonText = sComparisonText & Chr(34) & Chr(34) & Chr(34) & Chr(34) & Chr(34) & Chr(34)
End If
Set rResult = rCT.Columns(InputColumn).Find(What:=sComparisonText, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If (rResult Is Nothing) Then  'Add a new row to the bottom of the rCT range

Can someone tell me what I'm doing wrong?

Thank you very much! Dave

Just checking if there's a typo here - if you said sComparisonText initially contains (8"), you're not stripping off the final (") but instead adding 6 more double quotes, for a total of 7? — Andrew Arace, May 05 '15 at 19:44
Hi Andrew, You are correct, I'm not stripping the final ("), just adding more to it. I've tried (desperation) adding from 1 to 6 (") characters, but nothing I've tried will make the .find method actually find the cell. Thank you! — Dave 2.71828, May 05 '15 at 19:54

Gary's Student · Answer 1 · 2015-05-05T20:17:58.233

The first thing to consider is using xlPart rather than xlWhole

The second thing is to verify you really have double-quotes rather than a pair of single quotes. Click on the miscreant cell and run:

Sub WhatIsInThere()
    Dim st As String, msg As String
    Dim i As Long, CH As String

    st = ActiveCell.Text
    msg = Len(st)
    For i = 1 To Len(st)
        CH = Mid(st, i, 1)
        msg = msg & vbCrLf & CH & vbTab & Asc(CH)
    Next i
    MsgBox msg
End Sub

To see an example of finding something with a trail double-quote, start with an empty worksheet and run:

Sub EightInchNails()
    Dim DQ As String, WhereIsIt As Range
    DQ = Chr(34)
    Range("A15").Value = "8" & DQ

    Set WhereIsIt = Range("A:A").Find(what:="8" & DQ, after:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart)

    If WhereIsIt Is Nothing Then
    Else
        MsgBox WhereIsIt.Address(0, 0)
    End If
End Sub

1) I though xlPart would match a cell with more content than just the search string, whereas xlWhole required the match to be with the entire contents of the cell. Functionally, I need the later. Have I misunderstood the use of the constants xlPart and xlWhole? 2) Your WhatIsInThere() utility worked great! It reported pretty much what we'd expected: a 2 character long string, 1st char = ASCII 8 or Decimal 56, and the 2nd char = ASCII " or Decimal 34. — Dave 2.71828, May 05 '15 at 20:27

score 1 · Accepted Answer · answered May 05 '15 at 23:17

It isn't clear why you are trying to escape something that isn't a string literal. The reason you need to escape double-quotes in a string literal is so the compiler can parse it. The .Find function only expects a single " if you are only looking for a single ". If you already have a string stored in a variable that contains " in the string, use that. If you need to add one to a string, you can either use Chr$(34) or the escaped string literal """". They give you exactly the same resulting string:

Dim sComparisonText As String
Dim rResult As Range

sComparisonText = 8 & Chr$(34) 
Set rResult = ActiveSheet.Cells.Find(What:=sComparisonText, LookIn:=xlValues, _
              LookAt:=xlWhole, SearchOrder:=xlByColumns, _
              SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
Debug.Print rResult.Address

...is the same as...

sComparisonText = "8"""
Set rResult = ActiveSheet.Cells.Find(What:=sComparisonText, LookIn:=xlValues, _
              LookAt:=xlWhole, SearchOrder:=xlByColumns, _
              SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
Debug.Print rResult.Address

...is the same as...

sComparisonText = 8 & """"

...etc.

The escape sequence has no meaning outside the compiler.

score 0 · Answer 3 · answered May 05 '15 at 19:51

0

range.find "8"""

Should do the trick. The first two quotes at the end escape the actual " character, and the third quote terminates the string.

answered May 05 '15 at 19:51

Kyle

1

Your suggestion works! I can use it hard-coded into the range.find as you've shown above. I can also use the string constant "8""" to set a variable which is then used in the range.find line: `sComparisonText = "8"""`. Unfortunately, what I can not make work is to take the original contents of `sComparisonText` (the 2 chars: 8") and have vba add a leading double quote and a pair of trailing double quotes. The value shown in the watch window appears correct (although surrounded by and extra pair of quotes as the watch window always does). – Dave 2.71828 May 05 '15 at 20:42
Assuming that sComparisonText starts with the value '8"': `sComparisonText = """" & sComparisonText & """"""` Escaping quotes in VBA is kind of weird. – Kyle May 05 '15 at 20:53

Excel 2007 vba range.find method can't locate cell containing: 8" (2 chars long, pronounced: 8 inches)

3 Answers3