[RESOLVED] Trying to search excel and return a count number, any ideas to help jump start me?

[heyu]

I can open and read and write to excel, but I need some help on how to search through column A to find a particular text such as B22-11, once found then I need to get the row number (count). If anyone here can help jump start me on this that would be great.

Thanks.

[ForumAccount]

What do you have so far? Really all you will be doing is a for loop incrementing the row number. Is this what you need help with?

[heyu]

What do you have so far? Really all you will be doing is a for loop incrementing the row number. Is this what you need help with?

Well I am using some of the code that you had help me on a week or so ago. I am just not sure what the key item names are for these in the if statement:

Imports Excel = Microsoft.Office.Interop.Excel
 
Public Class myClass
 
  Private Sub UpdateBay_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateBay.Click
 
       
 
    Dim myPath As String = ("C:\Test.xls")
    Dim xlApp As Excel.Application = New Excel.ApplicationClass
    Dim xlWorkBook As Excel.Workbook = xlApp.Workbooks.Open(myPath)
    Dim xlWorkSheet As Excel.Worksheet = xlWorkBook.Worksheets("Status")
    Dim myDate As DateTime = Date.Now
 
        If Collum A = some_variable_String Then
       get row number/count and set it to variable myCount
       MsgBox(xlWorkSheet.Range("A" & myCount).Value)
       xlWorkSheet.Range("J" & myCount).Value = "Done " & myDate.Date
       xlWorkSheet.Range("A" myCount:M" & myCount).Interior.ColorIndex = 35
 
        Else
 
    Increment down one cell?
 
 
        xlWorkBook.Close()
        xlApp.Quit()
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)
 
    End Sub
 
   Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
 
End Class

[Bulldog]

You could do this;

        Dim LastRow As Integer = xlSheet.UsedRange.Rows(xlSheet.UsedRange.Rows.Count).Row
        Dim LastCol As Integer = xlSheet.UsedRange.Columns(xlSheet.UsedRange.Columns.Count).Column

        'Assuming ColumnNumber is the index of the column youre searching
        For i As Integer = 1 To LastRow
            If Not IsNothing(xlSheet.Application.Cells(i, ColumnNumber).Value) _
            AndAlso xlSheet.Application.Cells(i, ColumnNumber).Value.ToString = "A_String" Then
                'Do something
            End If
        Next

The row number will be the value of i when the string is found. If the string occurs more than once then you'll need to detect that.

[heyu]

OK I understand half of this but I am confused about two things:

What is "A_String" is this what I am searching for?

and what is ColumnNumber?

I am looking to search column "A" and I am looking for a certain string.

[heyu]

OK I got it now, but I need to figure out how to exit out of that for loop once I find something.

Public Class mySearch
 
    Private Sub UpdateBay_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateBay.Click
 
        Dim myPath As String = ("C:\Test.xls")
        Dim xlApp As Excel.Application = New Excel.ApplicationClass
        Dim xlWorkBook As Excel.Workbook = xlApp.Workbooks.Open(myPath)
        Dim xlWorkSheet As Excel.Worksheet = xlWorkBook.Worksheets("Status")
        Dim myDate As DateTime = Date.Now
 
        Dim mySearch As String = ("BP-" & TextBox1.Text)
        Dim LastRow As Integer = xlWorkSheet.UsedRange.Rows(xlWorkSheet.UsedRange.Rows.Count).Row
        Dim LastCol As Integer = xlWorkSheet.UsedRange.Columns(xlWorkSheet.UsedRange.Columns.Count).Column
 
        For i As Integer = 1 To LastRow
            If Not IsNothing(xlWorkSheet.Application.Cells(i, "A").Value) _
            AndAlso xlWorkSheet.Application.Cells(i, "A").Value.ToString = mySearch Then
                'Do something
                MsgBox(xlWorkSheet.Range("A" & i).Value)
                xlWorkSheet.Range("J" & i).Value = "Done " & myDate.Date
                xlWorkSheet.Range("M" & i).Value = "Sent " & myDate.Date
                'xlWorkSheet.Range("A89:M89").Interior.ColorIndex = 35
            End If
        Next
 
 
        xlWorkBook.Close()
        xlApp.Quit()
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)
 
 
 
    End Sub

[nmadd]

Exit For
http://msdn.microsoft.com/en-us/library/t2at9t47.aspx

[heyu]

Any Idea how I could do this, bellow but with a variable?

'xlWorkSheet.Range("A89:M89").Interior.ColorIndex = 35

N/G

xlWorkSheet.Range("A"&i):("M"&i).Interior.ColorIndex = 35

N/G

xlWorkSheet.Range("A"&i:"M"&i).Interior.ColorIndex = 35

Thanks.

[nmadd]

ws.Range("A" & i & ":M" & i).Interior.ColorIndex = 35

[heyu]

Thanks! How did ya figure that one out?

[heyu]

OK, one last problem I tried to add this to give me a message that string not found and it only half works that is if my search string is found it then also says not found. If the string is not found then it seems to work. Why is it that when a string is found both for loops execute?

For i As Integer = 1 To LastRow
            If Not IsNothing(xlWorkSheet.Application.Cells(i, "A").Value) _
            AndAlso xlWorkSheet.Application.Cells(i, "A").Value.ToString = mySearch Then
                'Do something
                MsgBox(xlWorkSheet.Range("A" & i).Value)
                xlWorkSheet.Range("J" & i).Value = "Done " & myDate.Date
                xlWorkSheet.Range("M" & i).Value = "Sent " & myDate.Date
                xlWorkSheet.Range("A" & i & ":M" & i).Interior.ColorIndex = 35
            End If
            'MsgBox(LastRow)
        Next
 
        For i As Integer = 1 To LastRow
            If Not IsNothing(xlWorkSheet.Application.Cells(i, "A").Value) _
            AndAlso xlWorkSheet.Application.Cells(i, "A").Value.ToString <> mySearch Then
                'Do something
                MsgBox(mySearch & "  Not Found")
                Exit For
            End If
 
        Next

[heyu]

Well my original goal here has been solved thanks to all the great posts and ideas you guys gave me! Thanks to all.