VBA: Compare 2 cells from different workbook and publish the text right or wrong

[mrfrid]

Hi,
i'm not very good with vba, so i hope someone can help me with a macro that compares 2 values and write a string.

1. It shall compare the values from sheet 1 (column B) with sheet 2 (column B).
2. If it finds same value in sheet 1 and sheet 2 (column B) then it shall compare the values from sheet 1 (column C and D) with same columns in sheet 2 (column C and D) .
3. Now it shall publish "Right Price", if its same values from column C and D and "Wrong Price" if its not same values from column C and D. It shall publish this in sheet 2, column G and column H.

There can be everything from 100 to 100000 rows in the file, so i need it to loop throw entire sheets.

Hope someone can help me with this, and if there are any doubt, please let me know so will i try to explain a little more.

example from sheet 1:

A B C D E F NoN Download 012888 29 28 SE Netonnet NoN Download 013004 139 136 SE Netonnet NoN Download 013006 179 175 SE Netonnet NoN Download 013451 179 175 SE Netonnet NoN Download 013452 249 244 SE Netonnet NoN Download 013453 179 175 SE Netonnet NoN Download 013660 199 195 SE Netonnet NoN Download 013667 99 97 SE Netonnet NoN Download 013669 89 87 SE Netonnet NoN Download 013671 99 97 SE Netonnet NoN Download 013673 99 97 SE Netonnet NoN Download 013676 449 440 SE Netonnet NoN Download 013678 99 97 SE Netonnet NoN Download 013679 99 97 SE Netonnet

Example sheet 2: A B C D E F G H PDCI 195809 2690 2636 SE Netonnet PDCI 195811 2690 2636 SE Netonnet PDCI 195821 3790 3714 SE Netonnet PDCI 195833 3490 3420 SE Netonnet PDCI 195835 3490 3420 SE Netonnet PDCI 195849 4790 4694 SE Netonnet PDCI 195851 4790 4694 SE Netonnet PDCI 195853 4990 4890 SE Netonnet PDCI 195855 4990 4890 SE Netonnet

[Leith Ross]

Hello mrfrid,

The attached workbook has the macro shown below added to it with a button on "Sheet2" to run it. Let me know if this meets your needs.

Sub CompareData()

    Dim j           As Long
    Dim k           As Long
    Dim MainData    As Variant
    Dim MainRng     As Range
    Dim MainWks     As Worksheet
    Dim Status      As Variant
    Dim TestData    As Variant
    Dim TestRng     As Range
    Dim TestWks     As Worksheet
    
        Set MainWks = Worksheets("Sheet1")
        Set TestWks = Worksheets("Sheet2")
        
        Set MainRng = MainWks.Range("A1").CurrentRegion
        Set TestRng = TestWks.Range("A1").CurrentRegion
        
        MainData = MainRng.Columns("B:D").Value
        TestData = TestRng.Columns("B:D").Value
        ReDim Status(1 To TestRng.Rows.Count, 1 To 2)
        
            For j = 1 To UBound(MainData)
                For k = 1 To UBound(TestData)
                    If TestData(k, 1) = MainData(j, 1) Then
                        If TestData(k, 2) = MainData(j, 2) And TestData(k, 3) = MainData(j, 3) Then
                            Status(k, 1) = "Right Price"
                        Else
                            Status(k, 2) = "Wrong Price"
                        End If
                    End If
                Next k
            Next j
            
        TestRng.Columns("G:H").Value = Status
        
End Sub

[mrfrid]

Hello mrfrid,

The attached workbook has the macro shown below added to it with a button on "Sheet2" to run it. Let me know if this meets your needs.

Sub CompareData()

    Dim j           As Long
    Dim k           As Long
    Dim MainData    As Variant
    Dim MainRng     As Range
    Dim MainWks     As Worksheet
    Dim Status      As Variant
    Dim TestData    As Variant
    Dim TestRng     As Range
    Dim TestWks     As Worksheet
    
        Set MainWks = Worksheets("Sheet1")
        Set TestWks = Worksheets("Sheet2")
        
        Set MainRng = MainWks.Range("A1").CurrentRegion
        Set TestRng = TestWks.Range("A1").CurrentRegion
        
        MainData = MainRng.Columns("B:D").Value
        TestData = TestRng.Columns("B:D").Value
        ReDim Status(1 To TestRng.Rows.Count, 1 To 2)
        
            For j = 1 To UBound(MainData)
                For k = 1 To UBound(TestData)
                    If TestData(k, 1) = MainData(j, 1) Then
                        If TestData(k, 2) = MainData(j, 2) And TestData(k, 3) = MainData(j, 3) Then
                            Status(k, 1) = "Right Price"
                        Else
                            Status(k, 2) = "Wrong Price"
                        End If
                    End If
                Next k
            Next j
            
        TestRng.Columns("G:H").Value = Status
        
End Sub

Thanks Leith Ross,
this was exactly what i wanted, thanks a million times

[Leith Ross]

Hello mrfrid,

You're welcome. Good to know I got it right.