How to verify that each element of a range in a worksheet is also present in another range in anothe-CodePudding

So earlier this week someone here gave me a really helpful code to check that the values in column B in sheet Daily1 are also inside column C in sheet Task List. The problem I am having is that the code seems to not be checking certain values, for example, Blue cheese control panel is in column B in B13 of sheet Daily1 and it is also in sheet Task List in Column C at cell C82 but the program returns a not found. I tried looking at the logic of the code and I can't seem to find any issues with it. It should work but I don't know why it isn't working.

Once again the code was kindly provided by someone else so I could be missing something.

Sub ExtractParts()
    Dim wsSrc As Worksheet: Set wsSrc = Sheets("Daily1")
    Dim wsDest As Worksheet: Set wsDest = Sheets("Task List")
    Dim LastRowSrc As Long: LastRowSrc = wsSrc.Cells(wsSrc.Rows.Count, "B").End(xlUp).Row
    Dim LastRowDest As Long: LastRowDest = wsDest.Cells(wsDest.Rows.Count, "C").End(xlUp).Row
    Dim i As Long, j As Long, rowRangeSrc As Range, rowRangeDest As Range
    
    Set rowRangeSrc = wsSrc.Range("B7:B" & LastRowSrc)
    Set rowRangeDest = wsDest.Range("C2:C" & LastRowDest)
    
    i = 2
    j = 7
    For Each rrow In rowRangeSrc
        If WorksheetFunction.CountIf(rowRangeDest, rrow.Value) = 0 Then
            wsDest.Cells(i, 5).Value = "Not Found"
        Else
           If wsSrc.Cells(j, 9).Value = "CR" Then
                wsDest.Cells(i, 5).Value = "Completed"
           Else
                wsDest.Cells(i, 5).Value = "Not Completed"
           End If
        End If
        i = i   1
        j = j   1
    Next rrow
    
End Sub

I have also attached pictures of the excel to see what I am referring to.

CodePudding user response：

there is no problem with the code and data.

here the code is iterating on rows of column A in sheet Daily1 and writing it to sheet Task List.

so the value not found is not for Blue cheese control panel in column C in sheet task list but whatever is in the last row of column A of sheet Daily1

update these two lines in your code for it to work as you intend.

For Each rrow In rowRangeDest
    If WorksheetFunction.CountIf(rowRangeSrc, rrow.Value) = 0 Then