Check if a value is in a range

[Alexis]

Hi everyone,

This is my first post. I've searched and I couldn't find the answer although I think it's probably pretty simple! Apologies if it's already out there.

I've got 2 lists of locations. Both have a location reference and a location name. I need to check to see if all of the locations in List2 are in List1. If there are any missing, maybe a message box can display which ones - not too bothered how this is done at this stage.

The context is that List1 is the front page of a commission calculation workbook and List2 is the full list of locations which comes from a SQL query. There are a lot of worksheets in the workbook, I've uploaded just some simple sample data.

This is the first part of a big procedure I'm trying to put together to automate (as much as possible) a process which just took me 3 days! Not got very far before needing help!

Thanks in advance.

Alexis

 

[Luke M]

Adding on to your existing macro, I think this macro would work. Could easily pass the info to a message box or store them in some other cells or something. But I think the question more about how to find them.

Sub CheckAllBranchesPresent()
 
'This procedure checks that all branches
' on the x ref sheet are on the front sheet
 
Dim Front As Worksheet
Dim X_Ref As Worksheet
Dim FrontBranches As Range
Dim X_RefBranches As Range
Dim fCell As Range, c As Range
Dim notFound As String
 
Set Front = Sheet1
Set X_Ref = Sheet2
Set FrontBranches = Front.Range("A2", "A42")
Set x_ref_branches = X_Ref.Range("A2", "A43")
 
 
For Each c In x_ref_branches
    Set fCell = Nothing
    Set fCell = FrontBranches.Find(c)
    If fCell Is Nothing Then
        'Value not found in list
        notFound = notFound & ", " & c.Value
    End If
Next c
 
If Len(notFound) > 0 Then
    MsgBox "The following were not found:" & _
        vbNewLine & mid(notfound,3) 'To chop off leading comma
End If
   
End Sub

 

[Marc L]

Hi,​

​

same in another way :​

Sub CheckRef()
    Dim Cel As Range, Ref As Range
    Set Ref = Sheet1.[A1].CurrentRegion.Columns(1).Cells
 
    For Each Cel In Sheet2.[A1].CurrentRegion.Columns(1).Cells
        If IsError(Application.Match(Cel.Value, Ref, 0)) Then _
            M$ = M$ & Cel.Address(0, 0) & "  :   " & Cel.Value & " - " & Cel(, 2).Value & vbLf
    Next
 
    Set Ref = Nothing
    If M > "" Then MsgBox M & Space(58), , "   Sheet2 missing Ref in Sheet1 :"
End Sub

 

[SirJB7]

@SirJB7
Hi, myself!
So long...
Oh, how much I miss this kind of people and feel happy when I find a new member that acts like Alexis...
- introduced himself
- pointed out that it was his 1st interaction
- performed the initial search(!) and apologized if not done properly
- exposed clearly the problem
- thanked in advance
- posted a sample (!)
Regards!

@Alexis
Hi!
First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.
Regards!

 

[Alexis]

Forgot to say that I had started writing some code out before I got stuck, sorry!

@Luke M
Thanks very much - this works great and thanks for adding it to the code that I had started.

@Marc L
Thanks for your solution too - this also works and I like the detail in the message box.

@SirJB7
Thank you for your kind welcome, glad to be here!

I'll be posting some other questions in due course I'm sure.
Thanks again,
Alexis