Listing multiple values in a macro
 

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

Listing multiple values in a macro
 

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

Listing multiple values in a macro
 

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

Listing multiple values in a macro
 

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

Listing multiple values in a macro
 

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

Listing multiple values in a macro
 

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

Listing multiple values in a macro
 

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