Listing multiple values in a macro

[ToddS]

Can't get this macro to work. Has something to do with the way I'm listing
the values but I don't know how to do this properly. Also, I need to be able
to list over 60 values (not just the 3 shown below), i just truncated for
this post. Please help!!!

Sub CheckNNumbers()
Dim RowNdx As Long
Dim LastRow As Long
StartRow = 2
LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "R").Value = "N117CH" Or "N917SH" Or "N911WK" Then
MsgBox "Invalid Aircraft No."
End If
Next RowNdx
End Sub

Again, I just need to know how to "list" multiple values. I've tried using
commas in place of the "Or" statements but nothing works! Please help!!!

 

[Don Guillett]

I might take a different approach by looking in the 60 item list (col K) for
each hit in the lookin list (col C). Something like this.

Sub findeachinlist()
On Error Resume Next
slr = Cells(Rows.Count, "k").End(xlUp).Row
On Error Resume Next
For Each c In Range("k3:k" & slr)
With Range("c1:c15")
Set c = .Find(c, lookat:=xlWhole, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
MsgBox c & " found at " & c.Row
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next c

End Sub

 

[ToddS]

Not following you. Do i need to create another column for the lookin list?
Not really what I had in mind. Plus, my list of N-numbers will vary in
length on a monthly basis. Is there not a way to make my macro work?

 

[Don Guillett]

If that's what you want, Sure...
If Cells(RowNdx, "R") = "N117CH" Or _
cells(RowNdx, "R")="N917SH" Or _
cells(RowNdx, "R")= "N911WK" Then
'==============
However, you may still like my approach better. Make your list IN the macro
with an array, if desired.

Sub findeachinlist()
On Error Resume Next
'slr = Cells(Rows.Count, "k").End(xlUp).Row
'For Each c In Range("k3:k" & slr)
'=================
myarray = Array("N117CH","N917SH","N911WK")
For Each c In myarray
'===============
With Range("c1:c15")
Set c = .Find(c, lookat:=xlWhole, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
MsgBox c & " found at " & c.Row
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next c

End Sub

 

[ToddS]

I tried using Or_ to list them but after listing about 20 of them I got an
error message in VB telling me that was too many. I'll try your way and see
what happens.

 

[ToddS]

Don - Unfortunately, yours didn't work. Any other ideas? I really
appreciate your help!

 

[Don Guillett]

I tested before sending.
Send your wb to my address below along with snippets of this msg and
complete desires and before/after examples.