Thread: Using VB To Find a String in an Excel Worksheet

I am working on an application where I need to locate a range of cells within a worksheet. Since the actual location of the data will vary for each worksheet, I have come to the conclusion that I can put a string near the range that I am looking for (i.e. "*SUMMARY*") and since I will know the location of the data that I am looking for relative to the location of this text string, it should work.

I have made some progress but am having trouble getting the Find method of the range component to work. My code is below. This code find each worksheet and shows me the name but I also need to to find the location of the cell with the string "*SUMMARY*" in it.

Any suggestions would be appreciated.

Thanks.

John

Imports Microsoft.Office.Interop.Excel


Public Class Form1
    Dim oExcelApp As New Microsoft.Office.Interop.Excel.Application
    Dim oWBa As Microsoft.Office.Interop.Excel.Workbook = oExcelApp.Workbooks.Open("C:\Temp\Care Center.xls")
    Dim oWS As Microsoft.Office.Interop.Excel.Worksheet
    Dim SearchArea As Range

    Sub ImportRanges()

        Dim StartCell As String

        oExcelApp.Visible = False

        For i = 1 To oWBa.Sheets.Count
            Debug.WriteLine("SheetName: " + oWBa.Sheets(i).name)
            oWS = oWBa.Sheets(oWBa.Sheets(i).name)
            SearchArea = oWS.Range(oWBa.Sheets(i).name).Select()
            StartCell = FindSummary()
            Debug.WriteLine("Summary Location: " + StartCell)
        Next

    End Sub
    Public Function FindSummary() As String

        Dim currentFind As Range = Nothing
        Dim firstFind As Range = Nothing

        ' You should specify all these parameters every time you call this method, 
        ' since they can be overridden in the user interface.
        'currentFind = oWS.Find("*SUMMARY*", , XlFindLookIn.xlValues, XlLookAt.xlPart, XlSearchOrder.xlByRows, XlSearchDirection.xlNext, False)
        currentFind = SearchArea.Find("*SUMMARY*")

        While Not currentFind Is Nothing

            ' Keep track of the first range you find.
            If firstFind Is Nothing Then
                firstFind = currentFind

                ' If you didn't move to a new range, you are done.
            ElseIf currentFind.Address = firstFind.Address Then
                Exit While
            End If

            currentFind = oWS.FindNext(currentFind)

        End While

        Return CStr(currentFind.Row) + CStr(currentFind.Column)

    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ImportRanges()

    End Sub
End Class

Is that VB? We don't do a lot of that here.

It doesn't appear that SearchArea is in scope in FindSummary. Is there a compiler directive to require variable declaration?

EDIT: I retract that. Sorry, I have nothing to contribute.