Multiple string search

[jgresko]

I am trying to search column L for letters "N-V" and if found replace it with the letter "P". I have been successful searching for one letter and changing it to "P", but don't want to have tons of code if not needed. Thanks!!

Sub Change_ReturnType()
'Changes return type N-V to P'
i = 2
Sheets("Listed").Select
Do While Range("A" & i) <> ""
If InStr(1, Range("L" & i), "N-V") > 0 Then
Range("L" & i) = "P"
End If
i = i + 1
Loop
End Sub

 

[Sektor]

Try this:

Sub Change_ReturnType()
    
    ' Set reference: Tools -> References -> Microsoft VBScript Regular Expressions 5.5
    
    Dim i As Integer, sh As Worksheet
    Dim re As New RegExp
    
    With re
        .Global = True
        .Pattern = "[N-V]"
    End With
    
    i = 2
    Set sh = Sheets("Listed")
    
    With sh
        Do While Not IsEmpty(.Cells(i, "A"))
            If re.test(.Cells(i, "L")) Then
                .Cells(i, "L") = re.Replace(.Cells(i, "L"), "P")
            End If
            i = i + 1
        Loop
    End With
    
End Sub

 

[jgresko]

The line below causes a compile error: User-defined type not defined. Did I type it wrong?

Dim re As New RegExp

 

[mikerickson]

Dim oneCell as Range

With Sheet1.Range("A:A")

    For Each oneCell in Range(.Cells(1,1), .Cells(.Rows.Count,1).End(xlup))
        If CStr(oneCell.Value) Like "[N-V]" Then
            oneCell.Value = "P"
        End If
    Next oneCell

End With

 

[Sektor]

Watch comment

 

[jgresko]

Mike, Your solution worked great. Thanks to all for the help.