PDA

View Full Version : Format search results in a Multicolumn Listbox With Headings



simora
03-15-2014, 10:28 PM
I have some code that finds the address of a Value entered into a InputBox on an excel UserForm.

I want to display the results in a Multicolumn Listbox with headings after it finds something.
The Headings Can be Name & Address;
I can get the address, & I also need to get to Column A to display a Name to match that Address and display both in the Listbox.

Right now, its displaying them under each other in the ListBox. How can I restructure so that it puts the found items next to each other as it loops through .

My Code:



Do
Set rngPicked = Union(rngPicked, rngFind)
Set rngFind = .FindNext(rngFind)

UserForm2.ListBox1.AddItem rngFind.Address ' Cell Address
UserForm2.ListBox1.AddItem Cells(loc, 1) ' Column A Name

Cnt = Cnt + 1
SearchRecords = Cnt

Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress

Bob Phillips
03-16-2014, 02:58 AM
With Userform2

.ListBox1.AddItem rngFind.Address ' Cell Address
.ListBox1..List(.ListBox1.ListCount - 1, 1) ' Column A Name
End With

snb
03-16-2014, 06:26 AM
sub M_snb()
listbox1.additem application.transpose(.FindNext(rngFind).resize(2))
End sub

But I'd prefer to store all found items into a multidimensional array ( e.g. 'sn') before populating the listbox with that array :


Listbox1.List=sn

simora
03-16-2014, 05:39 PM
Thanks Guys:
I got this one to work.




With ListBox1
.AddItem rngFind.Address ' Cell Address
.List(.ListCount - 1, 1) = (Cells(loc, 1))
End With


Info here helped.
http://stackoverflow.com/questions/6973287/adding-items-in-a-listbox-with-multiple-columns
AND ALSO http://vba4all.wordpress.com/2013/09/16/two-column-listbox/