Bug in Excel .Find function when interfacing with .NET

[Shankar_R]

Hi,

I ran into a problem where I run Excel from VB.NET and find some value in a named range. When the range has a single cell, the Find method returns cells which are not part of the range. In other words, different cells. I have not faced this problem in VBA. It was only during porting code from VBA that I came across this problem. Here is a sample code-

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices

Module Module1
    Dim XlApp As Excel.Application

    Sub Main()
        Try
            XlApp = New Excel.Application
            FindInSingleCell()
        Catch Ex As Exception
            MsgBox(Ex.Message)
        Finally
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
            GC.WaitForPendingFinalizers()
            Marshal.FinalReleaseComObject(XlApp)
        End Try
    End Sub



    Sub FindInSingleCell()
        Dim Wkb As Excel.Workbook, Wks As Excel.Worksheet
        Dim A1 As Excel.Range, MatchCell As Excel.Range

        Wkb = XlApp.Workbooks.Add
        Wks = CType(Wkb.Sheets("Sheet1"), Excel.Worksheet)

        With Wks
            .Cells(1, 1) = "Hi" 'cell A1
            .Cells(3, 1) = "Hi" 'cell A3
        End With
        A1 = XlApp.Range(Wks.Cells(1, 1), Wks.Cells(1, 1)) 'make the second cell different and the Find works fine

        MatchCell = A1.Find("Hi", LookAt:=Excel.XlLookAt.xlWhole) 'search should be restricted to only cell A1

        If Not MatchCell Is Nothing Then
            MsgBox("Matchcell has row = " & CStr(MatchCell.Row) & " and col = " & CStr(MatchCell.Column))
        Else
            MsgBox("No match found")
        End If
        Wkb.Close(SaveChanges:=False)
    End Sub


End Module 

When the program is run, it returns Matchcell row = 1 and col = 3 which clearly is outside the range A1.

Anyone else faced the same problem?

Shankar

[Shankar_R]

Googling this issue took me to a thread dated 2004, and even though it was logged as a bug back then, looks like it still persists.. I am using Office 2010 and VB 2010 Express.

http://www.excelbanter.com/showthrea...=300115&page=2

[westconn1]

do your lookat constants have the correct value when running in dot net ?
should be 1