Help Macro searching one range against another and clearing non matching cells is letting partial matches through :(

[Tardisgx]

E.g A cell containing "app" or "apple69" should be cleared; but they are getting matched to "apple"

This macro was posted by another user in a different forum and they will not reply. How can the match creteria be made Exact.

Option Explicit
Sub REMOVEINV()


    Dim LastRowD As Long
    Dim LastRowF As Long
    Dim LastColF As Long
    Dim key As Variant
    Dim i As Long
    Dim j As Long
    Dim MyDict As Object
    Dim SearchRng As Range
    Dim TempArr() As Variant
    Dim ws As Worksheet


    'set objects
    Set MyDict = CreateObject("scripting.dictionary")
    Set ws = ActiveSheet


    'set range bounds
    With ws
        LastRowD = .Cells(.Rows.Count, "D").End(xlUp).Row
        LastRowF = .Cells(.Rows.Count, "F").End(xlUp).Row
        LastColF = .Cells(LastRowF, .Columns.Count).End(xlToLeft).Column
        Set SearchRng = .Range(.Cells(2, 6), .Cells(LastRowF, LastColF))
    End With


    'set dictionary
    With MyDict
        .CompareMode = vbTextCompare
        For i = 2 To LastRowD
            key = Cells(i, 4).Value
            If Not key = vbNullString And Not .exists(key) Then
                .Add key, i
            End If
        Next i
    End With


    'fetch range data
    TempArr = SearchRng.Value


    'remove unwanted entries
    For i = LBound(TempArr, 1) To UBound(TempArr, 1)
        For j = LBound(TempArr, 2) To UBound(TempArr, 2)
            If Not TempArr(i, j) = vbNullString Then
                If Not MyDict.exists(TempArr(i, j)) Then TempArr(i, j) = vbNullString
            End If
        Next j
    Next i


    'plot array
    SearchRng = TempArr
    Set MyDict = Nothing


End Sub

 

[My Aswer Is This]

Tell us where you want to search for these values.
And what value you want to search for.
Are you saying you want to search for apple and look at all values in column A.

Please do not say look at script provided here and just modify it.

So if the script finds apple clear the cell.

 

[Peter_SSs]

When posting code, please use code tags (see my signature block below) as it makes the code much easier to read, debug & copy paste for testing. I've added them for you above.

Perhaps you had better post a small set of sample data and expected results as it seems to be working for me. Here is my 'before' and 'after'. Did the code produce the wrong results for me? Where/explain if so?

Book1
	D	E	F	G
1
2	car		apple69
3			car	rrr
4	apple		app	apple
5			app	iyiyu
Remove non-existing

Book1
	D	E	F	G
1
2	car
3			car
4	apple			apple
5
Remove non-existing