Solved: Looping required

[anandbohra]

Hi all
i have an collection object for which entries should be added to that collection only if it fulfills 2 criteria
first Column J should contains yes
second all the entries having yes in column J to be added to collection provided their column I should be between 80 to 90

Sub Flash_MWPL_yesonly()
Dim dateRay As Range
Dim yesRay As Range
Dim xVal, yval As Variant
Dim myColl As New Collection

With ThisWorkbook.Worksheets("Mktwdelmt")

Set dateRay = Range(.Range("i2"), .Range("i65536").End(xlUp))
Set yesRay = Range(.Range("j2"), .Range("j65536").End(xlUp))
End With

On Error Resume Next
For Each xVal In yesRay
If xVal.Value = "yes" Then
For Each yval In dateRay
If yval.Value >= Sheets("Mktwdelmt").Range("l2") _
And yval.Value <= Sheets("Mktwdelmt").Range("l1") Then
myColl.Add Item:=yval.Offset(0, -7) & vbTab & yval.Offset(0, 0)
End If
Next yval
End If

Next xVal
On Error GoTo 0

Sheets("Mktwdelmt").ListBox1.Clear

For Each xVal In myColl

MsgBox "Following Scrips have broken market Wide limits " & xVal, vbInformation + vbCritical, "Anand M. Bohra"
Sheets("Mktwdelmt").ListBox1.AddItem xVal
Next xVal

End Sub

i am not getting the exact syntax to be written in red font area

[Charlize]

Something like this. Better use 'option compare text' above your subs.Sub Flash_MWPL_yesonly()
' Dim dateRay As Range
Dim yesRay As Range
Dim xVal As Variant
Dim myColl As New Collection
With ThisWorkbook.Worksheets("Mktwdelmt")
' Set dateRay = Range(.Range("i2"), .Range("i65536").End(xlUp))
Set yesRay = Range(.Range("j2"), .Range("j65536").End(xlUp))
End With
On Error Resume Next
For Each xVal In yesRay
If xVal.Value = "yes" And xVal.Offset(, -1) > 79 And _
xVal.Offset(, -1) < 91 Then
myColl.Add Item:=xVal.Offset(0, -8) & vbTab & xVal.Offset(0, -1)
End If
Next xVal
On Error GoTo 0
Sheets("Mktwdelmt").ListBox1.Clear
For Each xVal In myColl
MsgBox "Following Scrips have broken market Wide limits " & xVal, _
vbInformation + vbCritical, "Anand M. Bohra"
Sheets("Mktwdelmt").ListBox1.AddItem xVal
Next xVal
End Sub

[anandbohra]

Thanks Charlize (http://vbaexpress.com/forum/member.php?u=5928)

i was very confused as to how to user nested for loop to get desired output