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.


Comments
Post a Comment