Clear contents of cell if value is present in specified column on different excel sheet

[jmk1153]

Hello,

I see others have asked similar questions to this but what I've found is based on defined values within the code or a single cell on the same sheet opposed to referencing all values contained in a column on a different sheet. I'm wondering if there's an efficient way to code the following:

If any cell in specified range (columns E:H) contains value from column N on Sheet2, clear that cell value.

e.g. there would be multiple cells in columns E, F, G and H that contain words contained in column N on sheet 2 and I'm trying to clear the cells with those values.

This would allow users to adjust column N on sheet 2 themselves without having to alter the code itself.

Appreciate any help!

 

[Akuini]

If any cell in specified range (columns E:H) contains value from column N on Sheet2, clear that cell value.

By contains do you mean the words match in whole or it could be in part?
Could you give an example?

 

[jmk1153]

By contains do you mean the words match in whole or it could be in part?
Could you give an example?

Ah, good question. To clarify, column N examples could be rad, kr, ply.

Some of the cells in columns E:H will match these words exactly and others will have additional characters but would still need to be identified and cleared.

Sheet1
E2: rad
E3: GGrad
F3: MMply

Sheet2
N1: rad
N2: kr
N3: ply

 

[Akuini]

If any cell in specified range (columns E:H) contains value from column N on Sheet2, clear that cell value.

Assuming data in both sheets start at row 2:

Sub jmk1153_1()
Dim i As Long, j As Long, n As Long, k As Long
Dim va, vb

Sheets("Sheet1").Activate 'change sheet name to suit

n = Range("E:H").Find("*", LookIn:=xlValues, lookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False).Row
va = Range("E2:H" & n)

With Sheets("Sheet2")
    vb = .Range("N2", .Cells(.Rows.Count, "N").End(xlUp))
End With

For k = 1 To UBound(vb, 1)
    For i = 1 To UBound(va, 1)
        For j = 1 To UBound(va, 2)
            If InStr(1, va(i, j), vb(k, 1), vbTextCompare) > 0 Then
                Debug.Print vb(k, 1) & " : " & i & " : " & j
                va(i, j) = ""
            End If
        Next
    Next
Next

Range("E2").Resize(UBound(va, 1), UBound(va, 2)) = va

End Sub

 

[jmk1153]

Assuming data in both sheets start at row 2:

Sub jmk1153_1()
Dim i As Long, j As Long, n As Long, k As Long
Dim va, vb

Sheets("Sheet1").Activate 'change sheet name to suit

n = Range("E:H").Find("*", LookIn:=xlValues, lookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False).Row
va = Range("E2:H" & n)

With Sheets("Sheet2")
    vb = .Range("N2", .Cells(.Rows.Count, "N").End(xlUp))
End With

For k = 1 To UBound(vb, 1)
    For i = 1 To UBound(va, 1)
        For j = 1 To UBound(va, 2)
            If InStr(1, va(i, j), vb(k, 1), vbTextCompare) > 0 Then
                Debug.Print vb(k, 1) & " : " & i & " : " & j
                va(i, j) = ""
            End If
        Next
    Next
Next

Range("E2").Resize(UBound(va, 1), UBound(va, 2)) = va

End Sub

Thank you for the help with this!

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.