I want to get Excel VBA to return, in a messagebox prompt, all items which are in column A of worksheet Mastersheet but are not in column A of worksheet DeliverySheet.

Having a lot of trouble figuring this one out, here's what I got so far:

Private Sub CommandButton5_Click()

    Dim DeliveryName As Range
    Dim MasterName As Range
    Dim MasterSheet As Worksheet
    Dim DeliverySheet As Worksheet
    Dim valueToFind

    Set MasterSheet = Sheets("Delivery Master List Drop")
    Set DeliveryName = Sheets("For Delivery").Range(Sheets("For Delivery").Cells("A:A"))
    Set MasterName = Sheets("Delivery Master List Drop").Range(Sheets("Delivery Master List Drop").Cells("A:A"))

    For i = 3 To 3000
        valueToFind = DeliveryName("i,1")

        For Each MasterName In MasterSheet
            If Not MasterName.Cells = valueToFind Then
                MsgBox "The following name is not found in the Delivery Master List" & DeliveryName(i, 1).Value, vbExclamation
            End If
        Next MasterName
    Next i
End Sub

I would preferably like the messagebox to return all items not found (which also don't happen to be values of ("")) in a list, which displays after macro is complete. Right now I am just trying to get it to even return one value.

  • 1
    Even after the edits I'm not sure what you're trying to do. Where's the question? This is a Q&A site, and while I get the jist that you're having a problem, I don't see a question. – YetAnotherRandomUser Nov 11 '18 at 3:09
  • This would be way easier with a helper column. You put a formula in one of the sheets that says something like =IF(A1='For Delivery'!A1,0,1) then you can just look at that column for 1's and output whatever you need for that row. You can even hide the column so users can't see your check. – HackSlash Nov 15 '18 at 21:21
Sub SearchForDeliveryItems()

    Dim wksMaster As Worksheet, wksSearch As Worksheet
    Dim rngMaster As Range, rngSearch As Range

    Set wksMaster = Sheets("Delivery Master List Drop")
    Set wksSearch = Sheets("For Delivery")

    With wksMaster
        Set rngMaster = .Range("A1:A" & .Range("A1").SpecialCells(xlCellTypeLastCell).Row)
    End With

    With wksSearch
        Set rngSearch = .Range("A1:A" & .Range("A1").SpecialCells(xlCellTypeLastCell).Row)
    End With

    With rngMaster
        For Each cll In rngSearch
            Set c = .Find(cll.Value2, LookIn:=xlValues)
            If c Is Nothing Then
                MsgBox cll.Value2 & " not found in the Delivery Master List."
            End If
    End With

End Sub
  • Working code is always great, but, around here, we prefer answers that include some sort of explanation.   Look around, and look at the answers with the highest vote scores — you’ll see what I mean. – Scott Nov 12 '18 at 0:54

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.