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

 

[Fluff]

how about

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

 

[Peter_SSs]

That also works great,

If it works great, why have you changed it?

This is not the code I suggested:

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

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

So, before we even consider combining codes, shouldn't we understand/know what each one is really supposed to be doing individually?

What has changed?

What are you actually trying to do?

 

[sparkytech]

Good question. I am trying to figure out as much of this on my own, so as not to burden everyone with my changes, and also so I can try to be as self sufficient as possible. On some of the forums, users get upset if they have to design everything for someone. I wish I could post a screen capture.
Here is the reason for the changes and the overall goal: this is a worksheet to track project names, work orders, outages, dates, etc.
Column "B" = "Super Project" number - will always have number in cell
Column "C" = "Project Name" - will always have text in cell
Column "D" is "Project Description" - sometimes has text in cell
Columns E-G are numbers associated with and to identify these projects.
Columns "Q" and "S" are start and end dates
Column "Y" will be a hidden column containing the "type" of row (i.e: Super Project, Project, or Outage.) I was going to use this for my "sort" function.
After running the code, I realized that some projects do not yet have a description "D", or are yet missing values from "E-G", so the new code works 99% of the time.
The ultimate goal is to look at each row, and make a determination of it being a Super Project, Project, or Outage by looking at the row contents, and then sort the sheet so as to have a heirarchy of Super Project>Projects>Outages sorted lastly by date.
I didn't want to seem like I was expecting for someone else to do all of the work for me, but this is my ultimate goal. If you can help me get that point, it would be appreciated! Thanks again!

 

[Fluff]

How do we determine if a row is Super Project, Project, or Outage?

 

[sparkytech]

I hope this simplifies what I am trying to do:


Typical Data Entry / Classification criteria


Super Project = data in "B-C" column, "D" is ignored, "E-F" will all be blank
Project = data in "B-C" and "E-F" columns
Outage = "C" column text contains "REQ" somewhere in the cell, column "B", and "E-F" are ignored
Then I want to put the above classifications in "Y" and sort the sheet.

 

[Fluff]

Ok, how about

Sub ColY()
  With Range("Y2:Y" & Range("C" & Rows.count).End(xlUp).Row)
    .Formula = "=IF(COUNTA(B2:C2)=2,IF(E2&F2="""",""Sproject"",IF(COUNTA(E2:F2)=2,""Project"","""")),"""")"
    .Value = .Value
  End With
  With Range("C1", Range("C" & Rows.count).End(xlUp))
    .Offset(, 22).Value = Evaluate("if(isnumber(search(""REQ""," & .Address & ")),""Outage""," & .Offset(, 22).Address & ")")
  End With
End Sub

 

[sparkytech]

That works perfect, thanks!

 

[Fluff]

Glad we could help & thanks for the feedback