Finding a text value in a range

[Graham]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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

Graham