anandbohra
07-18-2007, 02:28 AM
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
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