{"id":659,"date":"2019-03-23T20:29:15","date_gmt":"2019-03-23T20:29:15","guid":{"rendered":"http:\/\/www.vbastring.com\/blog\/?p=659"},"modified":"2019-03-26T16:28:16","modified_gmt":"2019-03-26T16:28:16","slug":"how-to-compare-cells-with-vba","status":"publish","type":"post","link":"https:\/\/vbastring.com\/blog\/2019\/03\/23\/how-to-compare-cells-with-vba\/","title":{"rendered":"How To Compare Cells With VBA"},"content":{"rendered":"

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. <\/p>\n

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. <\/p>\n

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”.<\/p>\n

Here’s what the list looks like:<\/p>\n

\"\"<\/a><\/p>\n

“How do I compare two columns in Excel?”<\/p>\n

Here’s a way how you do it with VBA.<\/p>\n

1. We are going to set the “Master List” as a named range.<\/p>\n

\t\"\"<\/a><\/p>\n

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

\tFollow the following image:<\/p>\n

\t\"\"<\/a><\/p>\n

Here is the process:<\/p>\n

1\tSet cells A2:A37 into a named range
\n2\tSet 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”.<\/p>\n

How do you know if two cells match in Excel?<\/p>\n

This will identify the new cities and now we can filter them and add them to the “Master List”.
\n\"\"<\/a>\t<\/p>\n

Here’s the code:<\/p>\n

\r\n'by erik@loeblcomservices.com - 2019\r\n\r\nFunction GetNewEntries(Arg1) As Boolean\r\n    'Purpose: Find the new cities we did business in\r\n    \r\n    Dim cnn As Object\r\n    Dim rst As Object\r\n    Dim strSQL As String\r\n    Dim lngCount As Long\r\n    \r\n    Dim strValue As String\r\n    Dim blnNew As Boolean\r\n    \r\n    'Set up the connection to the Excel worksheet\r\n    Set cnn = CreateObject(\"ADODB.Connection\")\r\n    With cnn\r\n        .Provider = \"Microsoft.ACE.OLEDB.12.0\"\r\n        .ConnectionString = \"Data Source=\" & ThisWorkbook.Path & \"\\\" & ThisWorkbook.Name & \";\" & _\r\n            \"Extended Properties=\"\"Excel 12.0 Xml;HDR=YES\"\";\"\r\n        .Open\r\n    End With\r\n    \r\n    \r\n    'In order to do queries with a WHERE clause, you need to name the range, otherwise use the worksheet name.\r\n\r\n    'get the new list cell value\r\n    strValue = Arg1\r\n    \r\n    'check if that value is already one we've done business in.\r\n    strSQL = \"SELECT * FROM [MasterList] WHERE [Master List] ='\" & strValue & \"'\"\r\n    Set rst = cnn.Execute(strSQL)\r\n    \r\n    If rst.EOF Then\r\n        'new item\r\n        blnNew = True\r\n    Else\r\n        'already in the Master List\r\n        blnNew = False\r\n    End If\r\n    \r\n\r\n    \r\n    rst.Close\r\n    cnn.Close\r\n    \r\n    Set rst = Nothing\r\n    Set cnn = Nothing\r\n    \r\n    GetNewEntries = blnNew\r\n    \r\nEnd Function<\/pre>\n
Watch how it’s done:<\/p>\n