Check value exists in a range

[grabrail]

Hi, hopefully a quick one.

In VBA I want to check if a text value exists in a range, and if it does change the value of another cell to either Y or N

The code I have so far is

Private Sub check_nil_defects()
    Dim rng As Range
    Set rng = Worksheets("DPU Report Template").Range("D25:D39,I25:I38")

End Sub

I want to check if the Value "ADF" exists in the cells in the range ive put in, and if it does then a different cell, lets say C1, becomes "N", if the value doesnt exist the value should be "Y"

Not sure where to go next. I'm very new to VBA and have inherited this spreadsheet i am working on from a developer that is no longer around.

 

[Legacy 143009]

Private Sub check_nil_defects()
    Dim rngD As Range
    Set rngD = Worksheets("DPU Report Template").Range("D25:D39")
    Dim rngI As Range
    Set rngI = Worksheets("DPU Report Template").Range("I25:I39")
    Dim cel As Range
    Dim i As Integer
    i = 1
    For Each cel In rngD.Cells
        With cel
            If Instr(cel.Value, "ADF") > 0 Then
                Cells(i, 3) = "Y"
            Else
                Cells(i, 3) = "N"
            End If
        i = i + 1
        End With
    Next cel
    For Each cel In rngI.Cells
        With cel
            If Instr(cel.Value, "ADF") > 0 Then
                Cells(i, 3) = "Y"
            Else
                Cells(i, 3) = "N"
            End If
        i = i + 1
        End With
    Next cel
End Sub

 

[gifariz]

You need to split the range first when the range is combination of multiple range.
Because you seem new to VBA, I try adding explanation in each line

    Dim inRngStr
    Dim txt
    Dim outRngStr
    Dim inRngArr() As String
    Dim item
    Dim rngSubIn As Range
    Dim rngOut As Range
    Dim cel As Range
    Dim arr1()
    
    'Type here the locations of input & output range, and text to be found
    inRngStr = "B2:B3,C2:C3"
    txt = "ADF"
    outRngStr = "C1"
    
    'Split input range string by comma delimiter
    inRngArr = Split(inRngStr, ",")
    
    'Iterate for each sub range string in splitted input range
    For Each item In inRngArr
        
        'Set current sub range
        Set rngSubIn = Worksheets("Sheet1").Range(item)
        
        'Iterate for each cell in current range
        For Each cel In rngSubIn.Cells
        
            'Check if txt exists in current cell
            If Not IsError(InStr(txt, cel.Value)) Then
                
                'Change output range value into "Y" if condition is met
                Worksheets("Sheet1").Range(outRngStr).Value = "Y"
            
            Else
            
                'Change output range value into "N" if condition is not met
                Worksheets("Sheet1").Range(outRngStr).Value = "N"
                
            End If
            
        Next cel
        
    Next item