How to use Excel VBA find function and return a result

Question

HI all, new to excel VBA. Just attended a course.
Like to know how to return a result in new column if there is a match when finding a string of characters. Hope someone can help me out. Thanks.

Eg below:

Function is to find entire column A from start to end for any string "BL0", "LO0" and "LOS" and return the result in column B. If no match when finding, then result is "Missing" in column B.
Data Result
A B
1 BL001..... BL0
2 ...BL02.... BL0
3 ...LO0... LO0
4 ....LOS... LOS
5 ........... Missing

Answer 1

See if that works for you.

Sub FINDSTRING()
Dim TextString As Variant, LastRow As Long
Dim area As Variant
On Error Resume Next
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

For Each area In Range("A2:A" & LastRow)  
  
    If Left(area.Value, 3) = "BL0" Then  
       area.Offset(0, 1) = Left(area.Value, 3)  
       Else  
       area.Offset(0, 1) = ""  
      
        If Left(area.Value, 3) = "LO0" Then  
            area.Offset(0, 1) = Left(area.Value, 3)  
            Else  
            area.Offset(0, 1) = ""  
         
            If Left(area.Value, 3) = "LOS" Then  
                area.Offset(0, 1) = Left(area.Value, 3)  
                Else  
                area.Offset(0, 1) = ""  
    End If  
        End If  
            End If  
      
Next  

End Sub

Regards
John

Answer 2

Hi John,

Many thanks for your kind guidance.

But when I copied the below sub to the VBA Developer tried to run, nothing seem to have happened. Not sure what needs to be done other than the run button.

272843-test-data-in-excel.pdf

Sub FINDSTRING()
Dim TextString As Variant, LastRow As Long
Dim area As Variant
On Error Resume Next
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

For Each area In Range("A2:A" & LastRow)

 If Left(area.Value, 3) = "BL0" Then  
    area.Offset(0, 1) = Left(area.Value, 3)  
    Else  
    area.Offset(0, 1) = ""  
      
     If Left(area.Value, 3) = "LO0" Then  
         area.Offset(0, 1) = Left(area.Value, 3)  
         Else  
         area.Offset(0, 1) = ""  
         
         If Left(area.Value, 3) = "LOS" Then  
             area.Offset(0, 1) = Left(area.Value, 3)  
             Else  
             area.Offset(0, 1) = ""  
 End If  
     End If  
         End If  
      

Next
End Sub

Answer 3

I Can't help you, it works fine on my computer, see sample attached.