CODEWITHSUNDEEP
vbaexcel
DL1
DL1

Reputation: 201

check multiple values in cell

THE VBA noob is back. I need some help with some basic stuff i suppose for u pros out there.

I have a range (column H) with values such as

P595349 saf XD    
P5933 LP XD    
P5955 GW1 (GG+W+P+E) XD

what i want is a formula or a VBA code that look if the cell contain 59 and PD it should print Correct in column E and if not it should print Not Correct in column E.

What i have today is only a start but i dont know how to add the second value (59) in this case.

  Sub AddDashes()

  With ThisWorkbook.Worksheets("sheet1")

     Dim SrchRng As Range, cel As Range

     Set SrchRng = Range("A:a")

     For Each cel In SrchRng
         If InStr(1, cel.Value, "PD") Then
             cel.Offset(0, 3).Value = "Decided"
         Else
             cel.Offset(0, 3).Value = "not"        
         End If
     Next cel
 End With

 End Sub

Upvotes: 0

Views: 1853

Answers (2)

CallumDA
CallumDA

Reputation: 12113

Why not write it as a function instead? You can use =CheckValue(H1) in column E for the output your require

Function CheckValue(str As String)
    If InStr(str, "XD")>0 And InStr(str, "59")>0 Then
        CheckValue = "Correct"
        Exit Function
    End If
    CheckValue = "Not Correct"
End Function

This line alone should be enough to make your original code work though (not tested):

If InStr(str, "XD")>0 And InStr(str, "59")>0 Then

Upvotes: 2

Shai Rado
Shai Rado

Reputation: 33682

You could try something like the code below, using the Like operator and the wild-card *.

Option Explicit

Sub AddDashes()

Dim SrchRng As Range, cel As Range

With ThisWorkbook.Worksheets("sheet1")
    Set SrchRng = .Range("A:A") '<-- don't forget to fully qualify your Range    
    For Each cel In SrchRng
        If cel.Value Like "*PD*" And cel.Value Like "*59*" Then
             cel.Offset(0, 3).value = "Decided"
        Else
             cel.Offset(0, 3).value = "not"
        End If
    Next cel    
End With

End Sub

Upvotes: 2

Related Questions