PDA

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



mrfrid
01-13-2016, 03:39 AM
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
01-13-2016, 03:44 PM
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
01-14-2016, 01:42 AM
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
01-14-2016, 10:02 AM
Hello mrfrid,

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