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

Answer 1

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.