Displaying the results of a Search

M

mgallagjr

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
 
T

Tom Ogilvy

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.
 
M

mgallagjr

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top