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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ToddS" wrote in message
...
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!!!

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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ToddS" wrote in message
...
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!!!

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ToddS" wrote in message
...
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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ToddS" wrote in message
...
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!!!

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.

"Don Guillett" wrote:

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ToddS" wrote in message
...
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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ToddS" wrote in message
...
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 - Unfortunately, yours didn't work. Any other ideas? I really
appreciate your help!

"Don Guillett" wrote:

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ToddS" wrote in message
...
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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ToddS" wrote in message
...
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!!!

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

"Don Guillett" wrote:

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ToddS" wrote in message
...
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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ToddS" wrote in message
...
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!!!