display multicolumn box

  • Thread starter Thread starter Alen32
  • Start date Start date
A

Alen32

I have one number in cell a5. In cells A10:A250 i have the product numbers.
I want to find in area A10:a250 all samme numbers as in cell a5 and display
rows in multicolumn box(usreform) where these numbers are.

if it is possible I want to display only results from column a,c and f.
 
Alen

Here's an example of how to do that

Private Sub UserForm_Initialize()

Dim rFound As Range
Dim sFirstAdd As String
Dim rLook As Range
Dim rValue As Range

Set rValue = Sheet1.Range("A5")
Set rLook = Sheet1.Range("A10:A250")
Me.ListBox1.ColumnCount = 4

Set rFound = rLook.Find(rValue.Value, , , xlWhole)

If Not rFound Is Nothing Then
sFirstAdd = rFound.Address

Do
With Me.ListBox1
.AddItem rFound.Row
.List(.ListCount - 1, 1) = rFound.Value
.List(.ListCount - 1, 2) = rFound.Offset(0, 2).Value
.List(.ListCount - 1, 3) = rFound.Offset(0, 5).Value
End With

Set rFound = rLook.FindNext(rFound)
Loop Until rFound.Address = sFirstAdd
End If

End Sub

For more on the Find method
http://www.dicks-blog.com/archives/2004/03/29/the-find-method/

For more on populating mulitcolumn listboxes
http://www.dicks-blog.com/archives/2004/05/10/populating-multi-column-listboxcombobox/
 
Back
Top