VBA code to search and display results in multicolumn listbox
Tom
Don't worry. It's a little long code.
But with the corrected code now it gives me this error:
Run-time error '381':
Could not set the List property. Invalid property array index.
This is a tough one...
Juan
On Feb 6, 1:39 pm, Tom Ogilvy
wrote:
My fault. I didn't look at that part of your code:
.AddItem Cell.Offset(0, 46).Value
.List(0, 1) = Cell.Value
.List(0, 2) = Cell.Offset(0, 48).Value
.List(0, 3) = Cell.Offset(0, 46).Address
should be
.AddItem Cell.Offset(0, 46).Value
.List(.ListIndex, 1) = Cell.Value
.List(.ListIndex, 2) = Cell.Offset(0, 48).Value
.List(.ListIndex, 3) = Cell.Offset(0, 46).Address
--
Regards,
Tom Ogilvy
" wrote:
Correction: It populates all the columns in the first results, but
then only shows the first column results in the subsequent rows.
Juan
On Feb 6, 11:46 am, wrote:
Thanks for the help. It worked but, it only populates the first column
of the multicolumnlistbox.
Juan
On Feb 6, 11:24 am, Tom Ogilvy
wrote:
Private Sub btnSrch_Click()
Dim cell as Range
Dim sAddr as String
Dim sh as Worksheet
Set sh = ActiveWorkbook.Sheets("Registry")
set cell = sh.Range("a8:a2010").Find( _
What:=txtSrchTerm.Text, _
After:=Range("A2010"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not cell is nothing then
sAddr = cell.Address
do
With ListBox1
.AddItem Cell.Offset(0, 46).Value
.List(0, 1) = Cell.Value
.List(0, 2) = Cell.Offset(0, 48).Value
.List(0, 3) = Cell.Offset(0, 46).Address
End With
set cell = sh.Range("A8:A2010").Findnext(cell)
loop while cell.Address < sAddr
end if
end With
End Sub
--
Regards,
Tom Ogilvy
" wrote:
Ok, here is this one:
I would like to search for a particular data in a range and when it
finds it, say it found 10 ocurrences of the criteria, show theresults
in a multicolumnlistbox, being one of the column the cell address.
This is what I have until now:
Private Sub btnSrch_Click()
ActiveWorkbook.Sheets("Registry").Activate
Range("a8:a2010").Select
Selection.Find(What:=txtSrchTerm.Text, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False).Activate
With ListBox1
.AddItem
.List(0, 0) = ActiveCell.Offset(0, 46).Value
.List(0, 1) = ActiveCell.Value
.List(0, 2) = ActiveCell.Offset(0, 48).Value
.List(0, 3) = ActiveCell.Offset(0, 46).Address
End With
End Sub
' txtSrchTerm = a text box in the userform with the criteria to search
But that gives me only the first cell matching the criteria in the
listbox. How do I show all theresultsin thelistbox?- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
|