PDA

View Full Version : [SOLVED] Listbox conditional Adding Items



sujittalukde
11-08-2013, 02:30 AM
I am trying to populate a list box from a range if a condition is matched.
The data arrangement is given under



product
salesman
units
criteria


fax
brown
1
y


phone
smith
10
y


fax
jones
20
y


fax
smith
30
n


p
jones
40
n


pc
smith
50
y


fax
brown
60
y


phone
davis
70
n


pc
jones
80
y




I’m using the following code



Private Sub CommandButton1_Click()
Dim rng1 As Range
Dim oCell As Range
Dim sRng As Range

Set rng1 = Sheets("sheet1").Range("D1:D10")
With ListBox1
.ColumnCount = 4
End With
For Each oCell In rng1
If oCell.Value = "y" Then
If sRng Is Nothing Then
Set sRng = oCell.EntireRow
Else
Set sRng = Application.Union(sRng, oCell.EntireRow)
End If
End If
Next oCell
If Not sRng Is Nothing Then ListBox1.List = sRng.Value


End Sub


But this is only adding the items till a ‘n’ is found in the list.

I want the code should populate all the rows which satisfies ‘y’ in the column D

A workbook is attached.

I am using Excel 2003.

mikerickson
11-08-2013, 08:44 AM
Rather than build a discontinuous range (with way more columns than a listbox)


For Each oCell In rng1
If oCell.Value = "y" Then
With oCell.EntireRow
.AddItem .Range("A1").Value
ListBox1.List(ListBox1.ListCount - 1, 1) = .Range("B1").Value
ListBox1.List(ListBox1.ListCount - 1, 2) = .Range("C1").Value
ListBox1.List(ListBox1.ListCount - 1, 3) = .Range("D1").Value
End With
End If
Next oCell

The stopping at the first "n" is because the range you are building is discontinuous and only the first Area of that range is getting to the listbox.

sujittalukde
11-08-2013, 11:10 PM
Thanks. its working.