Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I Have Beter Code For Search Than This
I Have This Code To Run Search By User Form With Textbox To Enter
Search Text Then Return Result On 6 Columns Listbox , Code Working Ok BuT Sometimes If Too Much Data It Takes A Long Time , If Can Be Beter Code I will Be Very Grateful . thanks Private Sub TextBox1_Change() On Error Resume Next Dim KH_Range As Range Dim M As String Set KH_Range = Range("ITEMS_INFO") M = TextBox1.Text ListBox1.Clear If M = "" Then GoTo 1 W = ListBox1.ListCount With KH_Range KH_1 = .Rows.Count KH_2 = .Columns.Count Set A = Range(.Cells(1, 1), .Cells (.Rows.Count, .Columns.Count)).Find(M) If Not A Is Nothing Then F = A.Address Do ListBox1.AddItem ListBox1.List(W, 0) = Sheet2.Cells(A.Row, 1).Value ListBox1.List(W, 1) = Sheet2.Cells(A.Row, 2).Value ListBox1.List(W, 2) = Sheet2.Cells(A.Row, 3).Value ListBox1.List(W, 3) = Sheet2.Cells(A.Row, 4).Value ListBox1.List(W, 4) = Sheet2.Cells(A.Row, 5).Value ListBox1.List(W, 5) = Sheet2.Cells(A.Row, 6).Value W = W + 1 Set A = Range(.Cells(1, 1), .Cells(KH_1, KH_2)).FindNext(A) Loop While Not A Is Nothing And A.Address < F End If End With Set A = Nothing 1 End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I Have Beter Code For Search Than This
I have on my web site a VBA procedure named FindAll which will find
all occurrences of a value in a range an return a range object containing all the cells in which the value was found. See http://www.cpearson.com/excel/findall.aspx or download, unzip, and import http://www.cpearson.com/Zips/modFindAll.zip . Once you have the FindAll function in your project, you can do something like the following: Dim FoundCells As Range Dim R As Range Dim N As Long Set founcells = FindAll(SearchRange:=KH_Range, _ FindWhat:=M, LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByColumns, MatchCase:=False) If Not FoundCells Is Nothing Then For Each R In FoundCells listbox1.AddItem For N = 0 To 5 listbox1.List(w, N) = R.EntireRow.Cells(1, N + 1).Value End If w = w + 1 Next R End If Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Sat, 5 Dec 2009 04:31:28 -0800 (PST), "abu " wrote: I Have This Code To Run Search By User Form With Textbox To Enter Search Text Then Return Result On 6 Columns Listbox , Code Working Ok BuT Sometimes If Too Much Data It Takes A Long Time , If Can Be Beter Code I will Be Very Grateful . thanks Private Sub TextBox1_Change() On Error Resume Next Dim KH_Range As Range Dim M As String Set KH_Range = Range("ITEMS_INFO") M = TextBox1.Text ListBox1.Clear If M = "" Then GoTo 1 W = ListBox1.ListCount With KH_Range KH_1 = .Rows.Count KH_2 = .Columns.Count Set A = Range(.Cells(1, 1), .Cells (.Rows.Count, .Columns.Count)).Find(M) If Not A Is Nothing Then F = A.Address Do ListBox1.AddItem ListBox1.List(W, 0) = Sheet2.Cells(A.Row, 1).Value ListBox1.List(W, 1) = Sheet2.Cells(A.Row, 2).Value ListBox1.List(W, 2) = Sheet2.Cells(A.Row, 3).Value ListBox1.List(W, 3) = Sheet2.Cells(A.Row, 4).Value ListBox1.List(W, 4) = Sheet2.Cells(A.Row, 5).Value ListBox1.List(W, 5) = Sheet2.Cells(A.Row, 6).Value W = W + 1 Set A = Range(.Cells(1, 1), .Cells(KH_1, KH_2)).FindNext(A) Loop While Not A Is Nothing And A.Address < F End If End With Set A = Nothing 1 End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with search code | Excel Programming | |||
Search code? | Excel Programming | |||
VBA search code | Excel Programming | |||
Excel XP VBA code to search all macro code in Excel module for specific search string criteria | Excel Programming | |||
Excel XP VBA code to search all macro code in Excel module for specific search string criteria | Excel Programming |