I have a section in a procedure where I have put in words what I am trying to do. I have
a named range of unique text entries in a single column, about 16 values. What I am
trying to do is identify if any of the range of cells being looped as below have any of
the values in that range and then act acoordingly. I know I could probably use a hole
range of case arguments but it seemsa sledgehammer to crack a nut. Is there a VBA function
that can do this? I appreciate any help

Set rng = Range("A15:A30")
For n = 1 To 200
If (Cells(n, 18).value (is equal to any of the text values in rng ) Then
Statements etc

Kind Regards,
Graham

You could use code something like this...

Dim N As Long
Dim C As Range
Dim Rng As Range
Dim FirstAddress As String

Set Rng = Range("A15:A30")

With Rng
For N = 1 To 200
Set C = .Find(Cells(N, 18), LookIn:=xlValues)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
'
' YOUR STATEMENTS GO HERE
'
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address < FirstAddress
End If
Next
End With

Rick


"Graham" wrote in message
...
I have a section in a procedure where I have put in words what I am
trying to do. I have a named range of unique text entries in a single
column, about 16 values. What I am trying to do is identify if any of the
range of cells being looped as below have any of the values in that range
and then act acoordingly. I know I could probably use a hole range of case
arguments but it seemsa sledgehammer to crack a nut. Is there a VBA
function that can do this? I appreciate any help

Set rng = Range("A15:A30")
For n = 1 To 200
If (Cells(n, 18).value (is equal to any of the text values in rng ) Then
Statements etc

Kind Regards,
Graham

Many thanks for that Rick, I just couldn't get my head round it. Your help is much
appreciated.

Graham

Rick Rothstein (MVP - VB) wrote:
You could use code something like this...

Dim N As Long
Dim C As Range
Dim Rng As Range
Dim FirstAddress As String

Set Rng = Range("A15:A30")

With Rng
For N = 1 To 200
Set C = .Find(Cells(N, 18), LookIn:=xlValues)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
'
' YOUR STATEMENTS GO HERE
'
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address < FirstAddress
End If
Next
End With

Rick


"Graham" wrote in message
...
I have a section in a procedure where I have put in words what I am
trying to do. I have a named range of unique text entries in a single
column, about 16 values. What I am trying to do is identify if any of
the range of cells being looped as below have any of the values in
that range and then act acoordingly. I know I could probably use a
hole range of case arguments but it seemsa sledgehammer to crack a
nut. Is there a VBA function that can do this? I appreciate any help

Set rng = Range("A15:A30")
For n = 1 To 200
If (Cells(n, 18).value (is equal to any of the text values in rng ) Then
Statements etc

Kind Regards,
Graham