Re: Find Next in a Search
Hi,
I posted this the other day for somebody else, hope it helps
Code:
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
Re: Find Next in a Search
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
Re: Find Next in a Search
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
Re: Find Next in a Search
Ahh, I see.. interesting I'll give it a try.
Re: Find Next in a Search
Its for a stock list so that scenario is unlikely bit still very true.
Im getting System.NullReferenceException on both if statements.
Code:
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
Re: Find Next in a Search
Refer to post #8 in your other thread on this topic. The error is similar.
http://www.vbforums.com/showthread.p...rch-A-Workbook
Re: Find Next in a Search
So its the "value" being empty? What do I need to put in it :confused:
Re: Find Next in a Search
Hi,
well evan if a certain Product in Stock is empty it should be 0 and not Blank
well anyway try this change
Code:
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
Re: Find Next in a Search
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.
Re: Find Next in a Search
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
Re: Find Next in a Search
mine returns every blank cell and ignores every cell with data in
Re: Find Next in a Search
Quote:
Originally Posted by
callumwk
mine returns every blank cell and ignores every cell with data in
well then you are doing something wrong
here again with changes
Code:
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
2 Attachment(s)
Re: Find Next in a Search
Code:
'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 =
Attachment 151717
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.
Re: Find Next in a Search
Quote:
Originally Posted by
callumwk
Code:
'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 =
Attachment 151717
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 =
Quote:
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
Re: Find Next in a Search
Quote:
Originally Posted by
ChrisE
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.
1 Attachment(s)
Re: Find Next in a Search
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
Attachment 151719
regards
Chris
Re: Find Next in a Search
I understand your search is searching for exact terms. I have made some adjustments:
Code:
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
Re: Find Next in a Search
Re: Find Next in a Search
Indeed, is it just me or does your profile picture keep changing?
Re: Find Next in a Search
Quote:
Originally Posted by
callumwk
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