ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   VBA code to search and display results in multicolumn listbox (https://www.excelbanter.com/excel-programming/382672-vba-code-search-display-results-multicolumn-listbox.html)

[email protected]
VBA code to search and display results in multicolumn listbox
 
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 the results
in a multicolumn listbox, 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 the results in the listbox?

Tom Ogilvy
VBA code to search and display results in multicolumn listbox
 
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 the results
in a multicolumn listbox, 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 the results in the listbox?

[email protected]
VBA code to search and display results in multicolumn listbox
 
Thanks for the help. It worked but, it only populates the first column
of the multicolumn listbox.

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 -

[email protected]
VBA code to search and display results in multicolumn listbox
 
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 -

Tom Ogilvy
VBA code to search and display results in multicolumn listbox
 
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 -

[email protected]
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 -

Tom Ogilvy
VBA code to search and display results in multicolumn listbox
 
sorry, mental glitch. .ListIndex should be .Listcount - 1

This is tested and worked for me:


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(.ListCount - 1, 1) = cell.Value
.List(.ListCount - 1, 2) = cell.Offset(0, 48).Value
.List(.ListCount - 1, 3) = cell.Offset(0, 46).Address
End With
Set cell = sh.Range("A8:A2010").FindNext(cell)
Loop While cell.Address < sAddr
End If

End Sub

--
Regards,
Tom Ogilvy

wrote in message
ups.com...
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 -


All times are GMT +1. The time now is 12:53 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com