Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to return a value for a portion of a text string in a cell | Excel Discussion (Misc queries) | |||
Return a formula as text string to a cell | Excel Worksheet Functions | |||
Cell text based on 4 condition test | Excel Worksheet Functions | |||
If condition is true return sumproduct of two arrays | Excel Worksheet Functions | |||
If condition is true return sumproduct of two arrays | Excel Worksheet Functions |