Excel With VBA

How To Compare Cells With VBA

Here is the scenario, the boss comes in to you, and wants you to add the new cities you all did business in last month to your master list.

You are to send the new list to the marketing department, so you all can market to the new cities you did business in last month.

The “New List” has some new cities not in the “Master List”. Your job is to add only the new entries to the “Master List”.

Here’s what the list looks like:

“How do I compare two columns in Excel?”

Here’s a way how you do it with VBA.

1. We are going to set the “Master List” as a named range.

—>BTW, if you need to delete or edit your named range, you can use the “Formulas” > “Name Manager” to correct your error.

Follow the following image:

Here is the process:

1 Set cells A2:A37 into a named range
2 Set up a user defined function which will take the value of the “New List” and check if the value in the “New List” is already in the “Master List”.

How do you know if two cells match in Excel?

This will identify the new cities and now we can filter them and add them to the “Master List”.

Here’s the code:

'by erik@loeblcomservices.com - 2019

Function GetNewEntries(Arg1) As Boolean
    'Purpose: Find the new cities we did business in
    
    Dim cnn As Object
    Dim rst As Object
    Dim strSQL As String
    Dim lngCount As Long
    
    Dim strValue As String
    Dim blnNew As Boolean
    
    'Set up the connection to the Excel worksheet
    Set cnn = CreateObject("ADODB.Connection")
    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _
            "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
        .Open
    End With
    
    
    'In order to do queries with a WHERE clause, you need to name the range, otherwise use the worksheet name.

    'get the new list cell value
    strValue = Arg1
    
    'check if that value is already one we've done business in.
    strSQL = "SELECT * FROM [MasterList] WHERE [Master List] ='" & strValue & "'"
    Set rst = cnn.Execute(strSQL)
    
    If rst.EOF Then
        'new item
        blnNew = True
    Else
        'already in the Master List
        blnNew = False
    End If
    

    
    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
    
    GetNewEntries = blnNew
    
End Function

Watch how it’s done:

Let me know if you have any questions.

****************************************************


 

admin