Excel VBA - Search column for text and insert text in another cell

[sparkytech]

I need to create code to search column "C" for any cells that contain "REQ", and then insert the text "REQ" in the same row in Column "Y". My code is not working and any help would be appreciated!

If ActiveCell.Value == REQ" Then    ActiveCell.offset(0,25).Value = "REQ" End If

 

[Peter_SSs]

Will a col C cell contain exactly "REQ" or might it contain
ABC REQ
IT IS REQUIRED
req
It Is Required

 

[Frank_Excel]

Sub Test()

	Dim rngEnd      As Range
	Dim rngBeg      As Range
	Dim iCell       As Range
	Dim strSearch   As String


	Set rngBeg = Range("C1")
	Set rngEnd = Range("C" & Range("C1").End(xlDown).Row)


	strSearch = "REQ"


	For Each iCell In Range(rngBeg, rngEnd)
		If InStr(iCell.Value, strSearch) Then
			iCell.Offset(0, 25).Value = "REQ"
		End If
	Next iCell
	
End Sub

 

[sparkytech]

The cell will contain other text as well as "REQ.
@ Frank_Excel - I tried your code, but am having no luck so far.

 

[Peter_SSs]

The cell will contain other text as well as "REQ.

Try

Sub REQ()
  With Range("C1", Range("C" & Rows.Count).End(xlUp))
    .Offset(, 23).Value = Evaluate("if(isnumber(search(""REQ""," & .Address & ")),""REQ"","""")")
  End With
End Sub

 

[sparkytech]

Thanks Peter, that works perfect! How would I go about adding 2 other "text checks" with different text?

(i.e.: Look for REQ2, and put REQ2 in cell)

 

[Fluff]

How about

Sub REQ()
   Dim i As Long
   Dim Ary As Variant
   Ary = Array("REQ", "REQ2")
   For i = 0 To UBound(Ary)
      With Range("C1", Range("C" & Rows.count).End(xlUp))
        .Offset(, 23).Value = Evaluate(Replace("if(isnumber(search(#," & .Address & ")),#," & .Offset(, 23).Address & ")", "#", Chr(34) & Ary(i) & Chr(34)))
      End With
   Next i
End Sub

 

[sparkytech]

Thanks to all for the quick solutions, it is greatly appreciated! I should have clarified my request for 2 additional text checks. Basically, I am not sure how to add another 2 checks in this same routine. I am very new to VBA, so I figure a lot out by trial and error, and I am not sure how to add these additional functions.


If columns "C" and "D" contain data, and columns "F" AND "G" are both blank, then put "Sproject" in the "Y" column
If columns "C", "D", "F", and "G" all contain data, then put "Project" in column "Y"
Else do nothing

Thanks in advance!

 

[Peter_SSs]

Thanks to all for the quick solutions, it is greatly appreciated! I should have clarified my request for 2 additional text checks. Basically, I am not sure how to add another 2 checks in this same routine. I am very new to VBA, so I figure a lot out by trial and error, and I am not sure how to add these additional functions.


If columns "C" and "D" contain data, and columns "F" AND "G" are both blank, then put "Sproject" in the "Y" column
If columns "C", "D", "F", and "G" all contain data, then put "Project" in column "Y"
Else do nothing

Thanks in advance!

I would change to this approach

Sub ColY()
  With Range("Y2:Y" & Range("C" & Rows.Count).End(xlUp).Row)
    .Formula = "=IF(COUNTA(C2:D2)=2,IF(F2&G2="""",""Sproject"",IF(COUNTA(F2:G2)=2,""Project"","""")),"""")"
    .Value = .Value
  End With
End Sub

 

[sparkytech]

That also works great, thanks! So, hopefully just one more question: how can I combine these 2 routines into one? They each work great independently, but one will overwrite what the other just did. I also tweaked them a little to work better with possible entries on my sheet.


Need to combine this:

With Range("Y2:Y" & Range("C" & Rows.Count).End(xlUp).Row)
    .Formula = "=IF(COUNTA(C2)=1,IF(E2&F2="""",""Super Project"",IF(COUNTA(F2)=1,""Project"","""")),"""")"
    .value = .value
  End With

With this:

With Range("C1", Range("C" & Rows.Count).End(xlUp))
    .Offset(, 22).value = Evaluate("if(isnumber(search(""REQ""," & .Address & ")),""Outage"","""")")
  End With

Thanks again for all of the help! I am learning a lot by working through this.