Looping through a range(column) and checking to see it contains a "Y"

Posted by Nate Hamilton on October 11, 2001 10:45 AM

I was wondering if you could help me. I'm running into a little bit of a problem. Here's my code so far.

Sub ColumnLooper()
Range("N7:N5000").Select
For Each cell In Selection
If ActiveCell.Value = "Y" Then
MsgBox "The List Cost has changed more than" & Chr(13) & _
"the allowed changed percentage."
End If
Next
End Sub

Posted by Jerid on October 11, 2001 12:02 PM

Try this

Hi Nate, give this a try.

Sub ColumnLooper()

Dim iX As Integer

For iX = 1 To 5000
Range("N" & iX).Select
If UCase(ActiveCell.Value) = "Y" Then
MsgBox "The List Cost has changed more than" & vbCrLf & "the allowed changed percentage."
End If
Next iX
End Sub

Jerid

Posted by Nate on October 11, 2001 12:58 PM

Thanks

Thanks, it worked. Jerid, you wouldn't happen to know how to do the other problem that I posted would you? The problem should be posted just above this problem. Thanks again

Nate

Dim iX As Integer For iX = 1 To 5000 Range("N" & iX).Select If UCase(ActiveCell.Value) = "Y" Then MsgBox "The List Cost has changed more than" & vbCrLf & "the allowed changed percentage." End If Next iX

Posted by Anon on October 11, 2001 5:19 PM

I presume you want the message box to appear if there is at least one "Y" in range N7:N5000 and only to appear once (even if there is more than one "Y"). If so, you shouln't use a loop - it's inefficient since it wiil examine all the cells, even after a "Y" has already been found.
Try this instead :-

Dim Y As Range
Set Y = Range("N7:N5000").Find("Y")
If Not Y Is Nothing Then
MsgBox "The List Cost has changed more than" & Chr(13) & _
"the allowed changed percentage."
End If