test cell for text string; return value if condition is true
 

Am stuck on something...

A B C
D

Pit/Slave data data RETURN VALUE HERE
New Pit Req data data RETURN VALUE HERE
No Action Req data data RETURN VALUE HERE
Pits are broken

I would like example code to check cell A1 for the text string of
"PIT" (ie it might be "pits" or "OldPits" or just "pit")

I need to return text string "PIT" to cell A4 if the condition is
true.

I have a few other text strings I need to check in A1 also; "Slave"
will return "Slave" to A4, "gaurd" will return "gaurd" to A4 etc...

Need to loop through A1:A8900

Any help appreciated


Steve

test cell for text string; return value if condition is true
 

=IF(NOT(ISERROR(FIND("PIT",A1))),"PIT","")

etc.

FIND is case-sensitive. For case-insensitivity, use SEARCH.

--

Vasant


"Steve" wrote in message
...

Am stuck on something...

A B C
D

Pit/Slave data data RETURN VALUE HERE
New Pit Req data data RETURN VALUE HERE
No Action Req data data RETURN VALUE HERE
Pits are broken

I would like example code to check cell A1 for the text string of
"PIT" (ie it might be "pits" or "OldPits" or just "pit")

I need to return text string "PIT" to cell A4 if the condition is
true.

I have a few other text strings I need to check in A1 also; "Slave"
will return "Slave" to A4, "gaurd" will return "gaurd" to A4 etc...

Need to loop through A1:A8900

Any help appreciated


Steve

test cell for text string; return value if condition is true
 

Steve,

You are trying to search in column A and alter the contents of column A at the same time?
I assume you want to put the text string in column 4 in the same row it was found in.
If that is the case then give the following a try...
( you will have to change "arrMyList" to contain those items you are looking for _
and the value for lngNum to agree)
'----------------------------------------------
Sub AreTheyInside()
'Jim Cone 07/07/2004
Dim arrMyList As Variant
Dim objCell As Range
Dim objRange As Range
Dim lngNum As Long

Set objRange = Range("A1:A8900")
arrMyList = Array("Pit", "Slave", "gaurd", "Huey", "Dewey", "Louie")

For Each objCell In objRange
For lngNum = 0 To 5
If InStr(1, objCell.Text, arrMyList(lngNum), vbTextCompare) Then
objCell(1, 4).Value = arrMyList(lngNum)
Exit For
End If
Next 'lngNUm
Next 'objcell

Set objCell = Nothing
Set objRange = Nothing
End Sub
'---------------------------------

Regards,
Jim Cone
San Francisco, CA

"Steve" wrote in message ...

Am stuck on something...
A B C D

Pit/Slave data data RETURN VALUE HERE
New Pit Req data data RETURN VALUE HERE
No Action Req data data RETURN VALUE HERE
Pits are broken
I would like example code to check cell A1 for the text string of
"PIT" (ie it might be "pits" or "OldPits" or just "pit")
I need to return text string "PIT" to cell A4 if the condition is
true.
I have a few other text strings I need to check in A1 also; "Slave"
will return "Slave" to A4, "gaurd" will return "gaurd" to A4 etc...
Need to loop through A1:A8900
Any help appreciated
Steve