Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying the results of a Search
I have built a function that will find every instance of a record, and
then store those records in an array. What I would like to do is have that information displayed in a window so that all the information matching that record is displayed. I am relatively new to VBA and do not know if this is even possible. Here is the code that finds my records and sets them in the array: Public Function BadgeSearch(myTXT As String, myCol As String, mycoL2 As String, myfiLler As Object) 'Searches for variable info that is provided by userform in the Master List worksheet and returns the row value for updating Badge = myTXT Myrec = 3 myfiLler.Clear ReDim myVal(0 To 0) As Integer ReDim Myval2(0 To 0) As Integer counTer = 0 Do Until Range(myCol & CStr(Myrec)).Text = "" Do While (Range(myCol & CStr(Myrec)).Text = Badge) If Range(myCol & CStr(Myrec)).Text = Badge Then myfiLler.AddItem (Range(mycoL2 & Myrec).Value) Myval2 = myVal ReDim myVal(0 To counTer) As Integer For z = 0 To UBound(Myval2) myVal(z) = Myval2(z) Next z ReDim Myval2(0 To counTer) myVal(counTer) = Myrec counTer = counTer + 1 End If Myrec = Myrec + 1 If Range(myCol & CStr(Myrec)).Text = "" Then Exit Do End If Loop Myrec = Myrec + 1 Loop End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying the results of a Search
Define Window
Anyway: Public Function BadgeSearch(myTXT As String, myCol As String, mycoL2 As String, myfiLler As Object) 'Searches for variable info that is provided by userform in the Master List worksheet and returns the row value for updating Dim s as String, i as Long Badge = myTXT Myrec = 3 myfiLler.Clear ReDim myVal(0 to 0) As Integer counTer = 0 Do Until Range(myCol & CStr(Myrec)).Text = "" Do While Range(myCol & CStr(Myrec)).Text = Badge If Range(myCol & CStr(Myrec)).Text = Badge Then myfiLler.AddItem Range(mycoL2 & Myrec).Value myVal(counTer) = Myrec counTer = counTer + 1 Redim Preserve myVal(0 to counTer) End If Myrec = Myrec + 1 If Range(myCol & CStr(Myrec)).Text = "" Then Exit Do End If Loop Myrec = Myrec + 1 Loop if ubound(myval,1) < 0 then redim preserve myval(0 to ubound(myval,1) - 1) end if s = "" for i = lbound(myval,1) to ubound(myval,1) s = s & myval(i) & vbNewLine Next msgbox s End Function Modifications Untested. -- Regards, Tom Ogilvy " wrote: I have built a function that will find every instance of a record, and then store those records in an array. What I would like to do is have that information displayed in a window so that all the information matching that record is displayed. I am relatively new to VBA and do not know if this is even possible. Here is the code that finds my records and sets them in the array: Public Function BadgeSearch(myTXT As String, myCol As String, mycoL2 As String, myfiLler As Object) 'Searches for variable info that is provided by userform in the Master List worksheet and returns the row value for updating Badge = myTXT Myrec = 3 myfiLler.Clear ReDim myVal(0 To 0) As Integer ReDim Myval2(0 To 0) As Integer counTer = 0 Do Until Range(myCol & CStr(Myrec)).Text = "" Do While (Range(myCol & CStr(Myrec)).Text = Badge) If Range(myCol & CStr(Myrec)).Text = Badge Then myfiLler.AddItem (Range(mycoL2 & Myrec).Value) Myval2 = myVal ReDim myVal(0 To counTer) As Integer For z = 0 To UBound(Myval2) myVal(z) = Myval2(z) Next z ReDim Myval2(0 To counTer) myVal(counTer) = Myrec counTer = counTer + 1 End If Myrec = Myrec + 1 If Range(myCol & CStr(Myrec)).Text = "" Then Exit Do End If Loop Myrec = Myrec + 1 Loop End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying the results of a Search
On Aug 6, 4:10 pm, Tom Ogilvy
wrote: Define Window What I am looking to do is take that search function, then take the row references that are stored in the array and display the data that is in the first 8 columns at each row returned to the array and display that info all in one userform would probably be a better description than a window. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displaying formula rather than results... | Excel Discussion (Misc queries) | |||
functions are not displaying the results | Excel Worksheet Functions | |||
I cant do a search on this forum. Everytime I search, it comes up with zero results | Excel Programming | |||
Sorting and displaying different results | New Users to Excel | |||
displaying results and not formulas | Excel Worksheet Functions |