Find Next in a Search

[callumwk]

Hey everyone,

I'm using this nice bit of code from "http://www.authorcode.com/search-text-in-your-excel-file-or-sheet-in-vb-net/"

It creates a search using a specified word

Private Sub SearchText()
        Dim oXL As Excel.Application
        Dim oWB As Excel.Workbook
        Dim oSheet As Excel.Worksheet
        Try
            Dim File_name As String = "C:\test.xls"
            oXL = CreateObject("Excel.Application")
            oWB = oXL.Workbooks.Open(File_name)
            oSheet = oWB.Worksheets(1)
 
            Dim oRng As Excel.Range = GetSpecifiedRange("get", oSheet)
            If oRng IsNot Nothing Then
                MessageBox.Show("Text found, position is Row-" & oRng.Row & " and column-" & oRng.Column)
            Else
                MessageBox.Show("Text is not found")
            End If
            oWB.Close()
 
            oSheet = Nothing
            oWB = Nothing
            oXL.Quit()
 
        Catch ex As Exception
            If oSheet IsNot Nothing Then
                oSheet = Nothing
            End If
            If oWB IsNot Nothing Then
                oWB = Nothing
            End If
            If oXL IsNot Nothing Then
                oXL.Quit()
            End If
        End Try
    End Sub

Which uses this function:

  Private Function GetSpecifiedRange(ByVal matchStr As String, ByVal objWs As Excel.Worksheet) As Excel.Range
        Dim currentFind As Excel.Range = Nothing
        Dim firstFind As Excel.Range = Nothing
        currentFind = objWs.Range("A1:AM100").Find(matchStr, , _
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, _
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, False)
        Return currentFind
    End Function

It all works rather well, but the webpage mentions the use of being able to "find next" and "find previous".

I've been trying for a while and can't figure out how to do it. Any Help?

Thanks

[ChrisE]

Hi,

I posted this the other day for somebody else, hope it helps

Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        
        Dim xlApp As New Application
        Dim xlWorkbook As Workbook
        Dim xlWorksheet As Worksheet
        xlWorkbook = xlApp.Workbooks.Open("C:\TestExcelToAccess.xls")
        xlWorksheet = CType(xlWorkbook.Worksheets("Sheet1"), Excel.Worksheet)

        'find the Last row:
        TextBox1.Text = xlWorkbook.Sheets(1).Cells(xlWorksheet.Rows.Count, 1).End(-4162).Row()

        'Test search A1:C Value from Textbox1
        Dim suchRng As Excel.Range = xlWorksheet.Range("A1:" & "C" & TextBox1.Text)
        Dim Row, Col As Long
        For Each rng As Excel.Range In suchRng
            If String.IsNullOrEmpty(rng.Value.ToString) Then Exit For
            If rng.Value.ToString = txtSearch.Text Then
                'add the Cell and the Text to Listbox
                ListBox1.Items.Add(rng.Address & " " & rng.Offset(Row, Col).Value)
            End If
        Next
        xlWorksheet = Nothing
        xlWorkbook = Nothing
        xlApp.Quit()
        xlApp = Nothing
    End Sub

regards
Chris

[callumwk]

Does that enable the user to see the next search result? I can't see if it does :/

Also, if it helps. My "next" and "previous" are on buttons so the user has to decide to want to see the next result.

Thanks again

[ChrisE]

Hi,

it will return all found Cells(in Listbox) with the searchtext that was entered in txtSearch.
I did not use a Button "next" and "previous"

Edit:
what if the entered searchtext would result in 45 find's
do you really want the user to click 45 times "next" and "previous" ?

regards
Chris

[callumwk]

Ahh, I see.. interesting I'll give it a try.

[callumwk]

Its for a stock list so that scenario is unlikely bit still very true.

Im getting System.NullReferenceException on both if statements.

Dim suchRng As Excel.Range = oSheet.Range("B1:B5500")
                Dim Row, Col As Long
                For Each rng As Excel.Range In suchRng
                    If String.IsNullOrEmpty(rng.Value.ToString) Then Exit For
                    If rng.Value.ToString = Form1.TextBox2.Text Then
                        'add the Cell and the Text to Listbox
                        ListBox1.Items.Add(rng.Address & " " & rng.Offset(Row, Col).Value)
                    End If
                Next

[TysonLPrice]

Refer to post #8 in your other thread on this topic. The error is similar.

http://www.vbforums.com/showthread.p...rch-A-Workbook

[callumwk]

So its the "value" being empty? What do I need to put in it

[ChrisE]

Hi,

well evan if a certain Product in Stock is empty it should be 0 and not Blank

well anyway try this change

 Dim suchRng As Excel.Range = xlWorksheet.Range("A1:" & "C" & TextBox1.Text)
        Dim Row, Col As Long
        Dim s As String
Edit: sorry this can go
       ' s = Nothing
        For Each rng As Excel.Range In suchRng
            If String.IsNullOrEmpty(rng.Value) Then
                rng.Value = Nothing
                ListBox1.Items.Add(rng.Address & "Empty " & rng.Offset(Row, Col).Value) 'add the Cell and state it's empty
            ElseIf rng.Value.ToString = txtSearch.Text Then
                'add the Cell and the Text to Listbox
                ListBox1.Items.Add(rng.Address & " " & rng.Offset(Row, Col).Value)
            End If
        Next

regards
Chris

[callumwk]

I don't think its searching properly... Might have to revert back to the "Find Next" option so I don't loose my sanity haha.

[ChrisE]

Hi,

well I tried it
I changed a couple of Cells in Excel to Blank Cells
and it returned my Searchtext and also added the empty cells to the Listbox

regards
Chris

[callumwk]

mine returns every blank cell and ignores every cell with data in

[ChrisE]

mine returns every blank cell and ignores every cell with data in

well then you are doing something wrong

here again with changes

  Dim xlApp As New Application
        Dim xlWorkbook As Workbook
        Dim xlWorksheet As Worksheet
        xlWorkbook = xlApp.Workbooks.Open("C:\TestExcelToAccess.xls")
        xlWorksheet = CType(xlWorkbook.Worksheets("Sheet1"), Excel.Worksheet)

        'find the Last row:
        TextBox1.Text = xlWorkbook.Sheets(1).Cells(xlWorksheet.Rows.Count, 1).End(-4162).Row()

        'Test search A1:C Value from Textbox1
        Dim suchRng As Excel.Range = xlWorksheet.Range("A1:" & "C" & TextBox1.Text)
        Dim Row, Col As Long

        For Each rng As Excel.Range In suchRng
            If String.IsNullOrEmpty(rng.Value) Then
                rng.Value = Nothing
                ListBox1.Items.Add(rng.Address & "Empty " & rng.Offset(Row, Col).Value)
            ElseIf rng.Value.ToString = txtSearch.Text Then
                'add the Cell and the Text to Listbox
                ListBox1.Items.Add(rng.Address & " " & rng.Offset(Row, Col).Value)
            End If
        Next
        xlWorksheet = Nothing
        xlWorkbook = Nothing
        xlApp.Quit()
        xlApp = Nothing

regards
Chris

[callumwk]

'find the Last row:
                TextBox5.Text = oWB.Sheets(1).Cells(oSheet.Rows.Count, 1).End(-4162).Row()

                'Test search A1:C Value from Textbox1
                Dim suchRng As Excel.Range = oSheet.Range("A1:" & "C" & TextBox5.Text)
                Dim Row, Col As Long

                For Each rng As Excel.Range In suchRng
                    If String.IsNullOrEmpty(rng.Value) Then
                        rng.Value = Nothing
                        ListBox1.Items.Add(rng.Address & "Empty " & rng.Offset(Row, Col).Value)
                    ElseIf rng.Value.ToString = Form1.textbox2.Text Then
                        'add the Cell and the Text to Listbox
                        ListBox1.Items.Add(rng.Address & " " & rng.Offset(Row, Col).Value)
                    End If
                Next

Text box 5 =
the last row containing text



list box =


the spreadsheet contains stock codes in column A and descriptions in B. I only want to search for matches in column B, but as you can see the list box only shows empty cells.

[ChrisE]

'find the Last row:
                TextBox5.Text = oWB.Sheets(1).Cells(oSheet.Rows.Count, 1).End(-4162).Row()

                'Test search A1:C Value from Textbox1
                Dim suchRng As Excel.Range = oSheet.Range("A1:" & "C" & TextBox5.Text)
                Dim Row, Col As Long

                For Each rng As Excel.Range In suchRng
                    If String.IsNullOrEmpty(rng.Value) Then
                        rng.Value = Nothing
                        ListBox1.Items.Add(rng.Address & "Empty " & rng.Offset(Row, Col).Value)
                    ElseIf rng.Value.ToString = Form1.textbox2.Text Then
                        'add the Cell and the Text to Listbox
                        ListBox1.Items.Add(rng.Address & " " & rng.Offset(Row, Col).Value)
                    End If
                Next

Text box 5 = the last row containing text



list box =


the spreadsheet contains stock codes in column A and descriptions in B. I only want to search for matches in column B, but as you can see the list box only shows empty cells.

I don't understand your Textbox5 =

Text box 5 = the last row containing text

in Textbox5 should be a number (number of rows in you Excelsheet)

I think I will upload my sample and you can see better.

regards
Chris

[callumwk]

I don't understand your Textbox5 =

in Textbox5 should be a number (number of rows in you Excelsheet)

I think I will upload my sample and you can see better.

regards
Chris

That's what I'm saying. Text box 5 contains the number of the last row containing text. I.E, the number of rows on the sheet.

[ChrisE]

Hi,

here a screenshot of my result. I have 78 rows in my Excelfile, you can see the
search text and the result in the listbox




regards
Chris

[callumwk]

I understand your search is searching for exact terms. I have made some adjustments:

 Dim rownum As Integer = oWB.Sheets(1).Cells(oSheet.Rows.Count, 1).End(-4162).Row()
                Dim suchRng As Excel.Range = oSheet.Range("B3:" & "B" & rownum)
                Dim Row, Col As Long

                For Each rng As Excel.Range In suchRng
                    If String.IsNullOrEmpty(rng.Value) Then
                        rng.Value = Nothing
                        ListBox1.Items.Add(rng.Address & "Empty " & rng.Offset(Row, Col).Value)
                    ElseIf InStr(LCase(rng.Value.ToString), Form1.TextBox2.Text, CompareMethod.Text) <> 0 Then
                        'add the Cell and the Text to Listbox
                        ListBox1.Items.Add(rng.Address & " " & rng.Offset(Row, Col).Value)
                    End If
                Next

[ChrisE]

working now ?

[callumwk]

Indeed, is it just me or does your profile picture keep changing?

[ChrisE]

Indeed, is it just me or does your profile picture keep changing?

Hi,
glad you got it working, I change my Profil picture a couple of days ago.

regards
Chris