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.
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.