Tags: action, cell, condition, excel, microsoft, msdn, pit, req, return, slave, software, string, stuck, text, value

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

On Microsoft » Microsoft Excel

3,404 words with 2 Comments; publish: Fri, 23 May 2008 07:14:00 GMT; (306140.63, « »)

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

All Comments

Leave a comment...

  • 2 Comments
    • =IF(NOT(ISERROR(FIND("PIT",A1))),"PIT","")

      etc.

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

      --

      Vasant

      "Steve" <stevepegg.excel.questionfor.info.telstra.com> wrote in message

      news:40ecb557.157658593.excel.questionfor.info.news.cdn.telstra.com.au...

      > 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

      #1; Fri, 23 May 2008 07:15:00 GMT
    • 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" <stevepegg.excel.questionfor.info.telstra.com> wrote in message news:40ecb557.157658593.excel.questionfor.info.news.cdn.telstra.com.au...

      > 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; Fri, 23 May 2008 07:16:00 GMT