How do I compare data but ask me where to compare

March 12, 2019 at 14:48:30
Specs: Windows 7
I have a workbook that I have a tab in it called "NEW REC" and I have another workbook that has data in it on sheet 1 that I need to compare.

Data in sheet called "NEW REC" has data in columns A,B,C,D and this also the case in other workbook as in data in columns A-D.

Im trying to make a compare macro that I can save to a personal macro so I can use it in different workbooks. I think thats correct?

I will always have the workbook with the tab NEW REC open and I donno if I can set it up so it asks me where to look for other sheet to open to be able to compare.
If I can I then need it to look down the sheet called "NEW REC" and compare against data in new workbook Ive opened.

If there are matches in rows down worksheet ive opened and also in NEW REC tab that are exactly the same I need them ignored in the tab "NEW REC" and if dont match highlight them in NEW REC tab.

What I mean by this is lets say row 5 columns A-D match with exactly the same data in workbook I opened in row 99 columns A-D then thats a match and ignore but as stated if nothing compares then need highlighted in new rec tab.


I had this macro but but it compares 2 sheets in the same workbook and as stated im trying to get it to ask me where I want to look to compare

Sub CompareData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim Val As String, ws1 As Worksheet, ws2 As Worksheet, i As Long, v1, v2, RngList As Object
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")
    LastRow = ws2.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v1 = ws1.Range("A2", ws1.Range("A" & Rows.Count).End(xlUp)).Resize(, 4).Value
    v2 = ws2.Range("A2", ws2.Range("A" & Rows.Count).End(xlUp)).Resize(, 4).Value
    Set RngList = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(v2, 1)
        Val = v2(i, 1) & "|" & v2(i, 2) & "|" & v2(i, 3) & "|" & v2(i, 4)
        If Not RngList.Exists(Val) Then
            RngList.Add Val, Nothing
        End If
    Next i
    For i = 1 To UBound(v1, 1)
        Val = v1(i, 1) & "|" & v1(i, 2) & "|" & v1(i, 3) & "|" & v1(i, 4)
        If Not RngList.Exists(Val) Then
            ws1.Rows(i + 1).EntireRow.Interior.ColorIndex = 6
        End If
    Next i
    Application.ScreenUpdating = True
End Sub


See More: How do I compare data but ask me where to compare

Reply ↓  Report •

#1
March 13, 2019 at 15:03:13
You didn't use any comments in your code, so I won't use any in mine.

Try this:

Sub CompareData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim Val As String, ws1 As Worksheet, ws2 As Worksheet, i As Long, v1, v2, RngList As Object
    Set ws1 = Sheets("Sheet1")
    v1 = ws1.Range("A2", ws1.Range("A" & Rows.Count).End(xlUp)).Resize(, 4).Value
    searchBook = Application.GetOpenFilename
    Workbooks.Open searchBook
    Set ws2 = ActiveWorkbook.Sheets("Sheet2")
    LastRow = ws2.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v2 = ws2.Range("A2", ws2.Range("A" & Rows.Count).End(xlUp)).Resize(, 4).Value
    Set RngList = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(v2, 1)
        Val = v2(i, 1) & "|" & v2(i, 2) & "|" & v2(i, 3) & "|" & v2(i, 4)
        If Not RngList.Exists(Val) Then
            RngList.Add Val, Nothing
        End If
    Next i
    For i = 1 To UBound(v1, 1)
        Val = v1(i, 1) & "|" & v1(i, 2) & "|" & v1(i, 3) & "|" & v1(i, 4)
        If Not RngList.Exists(Val) Then
            ws1.Rows(i + 1).EntireRow.Interior.ColorIndex = 6
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •
Related Solutions


Ask Question