Thread
:
Listing multiple values in a macro
View Single Post
#
5
Posted to microsoft.public.excel.worksheet.functions
ToddS
external usenet poster
Posts: 9
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!!!
Reply With Quote
ToddS
View Public Profile
Find all posts by ToddS