How to verify that each element of a range in a worksheet is also present in another range in another wokrsheet?

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.

enter image description here

enter image description here



Comments

Popular posts from this blog

Today Walkin 14th-Sept

Network Error and Timeout on Authorize.net JS

Spring Elasticsearch Operations