Macro If cell contains "X" enter value in Cell L

[Melrose0507]

Hello excel world,

I have a spreadsheet that has in column A has a lot of information grouped together. I want to view the text and if I find the letters "_ROS to enter "ROS" in column L. I also have other identifies I am looking for as well. In all cases only one of the following would be listed:
"_ROS" then cell L= "ROS"
"_HOM" then cell L="HOM"
"_SIG" then cell L="SIG"
"_ENT" then cell L="ENT"
There are about 4 other identifies that could be possible but I can manage that. Once again, the line would only contain one of these identifies. Where the identifier is located within the cell could be different as well so I need it to search the cell for these identifiers.

Thanks for the assistance.

 

[tigeravatar]

Melrose0507
Give the following a try. If the data has headers, change DataHeaders to True

Sub tgr()
    
    Const DataHeaders As Boolean = False
    Static arrCriteria As Variant: arrCriteria = Array("ROS", "HOM", "SIG", "ENT")
    
    Dim rngA As Range
    Dim ACell As Range
    Dim arrL() As String
    Dim arrIndex As Long
    Dim CriteriaIndex As Long
    
    If DataHeaders = True Then
        With ActiveSheet.UsedRange
            Set rngA = Intersect(.Offset(1).Resize(.Rows.Count - 1), Columns("A"))
        End With
    Else
        Set rngA = Intersect(ActiveSheet.UsedRange, Columns("A"))
    End If
    
    ReDim arrL(1 To rngA.Cells.Count)
    For Each ACell In rngA
        For CriteriaIndex = 0 To UBound(arrCriteria)
            If InStr(ACell.Value, "_" & arrCriteria(CriteriaIndex)) Then Exit For
        Next CriteriaIndex
        arrIndex = arrIndex + 1
        On Error Resume Next: arrL(arrIndex) = arrCriteria(CriteriaIndex)
    Next ACell
    
    Cells(rngA.Row, "L").Resize(UBound(arrL), 1).Value = WorksheetFunction.Transpose(arrL)
    
End Sub

Hope that helps,
~tigeravatar

 

[Melrose0507]

This is perfect. You have just saved me a large headache.

Thanks so much.