VBA: compare values in columns and list the missmatches in a new sheet

[Manonfire]

Hi

I'm new here and at VBA. My question goes:

I have 3 sheets(1, 2 and 3). At sheet 1 i have column A(range A2-end) with data that i want to compare with column A(range A2-end) and D(range D2-end) on sheet2. If a value in sheet 1 column A is not found on sheet 2 column A and D, then it should list the "missmatched" value in sheet 3 starting at Range A2.

I have tried everything but i can't get the result i want at Excel 2010
Thanks in advance.


Here is what i have:

Sub Makro5()

Dim lastRowE As Integer
Dim lastRowF As Integer
Dim lastRowM As Integer
Dim foundTrue As Boolean


Application.ScreenUpdating = False

lastRowE = Sheets("1").Cells(Sheets("1").Rows.Count, "A2").End(xlUp).row
lastRowE = Sheets("2").Cells(Sheets("2").Rows.Count, "A2").End(xlUp).row
lastRowF = Sheets("2").Cells(Sheets("2").Rows.Count, "D2").End(xlUp).row
lastRowM = Sheets("3").Cells(Sheets("3").Rows.Count, "A2").End(xlUp).row



For i = 1 To lastRowE
foundTrue = False
For j = 1 To lastRowF

If Sheets("1").Cells(i, 1).value = Sheets("2").Cells(j, 1).value Then
foundTrue = True
and
If Sheets("1").Cells(i, 1).value = Sheets("2").Cells(j, 4).value Then
foundTrue = True

Exit For
End If

Next j

If Not foundTrue Then

Sheets("3").Rows(i).Copy Destination:= _
Sheets("3").Rows(lastRowM + 1)
lastRowM = lastRowM + 1

End If

[6StringJazzer]

Please use CODE tags when posting code.

This code does not compile. There are several errors in the structure (missing Next, missing End Ifs)

The code sets lastRowE then in the next line of code set it to something else. So the first line has no effect.


lastRowE = Sheets("1").Cells(Sheets("1").Rows.Count, "A2").End(xlUp).row
lastRowE = Sheets("2").Cells(Sheets("2").Rows.Count, "A2").End(xlUp).row
lastRowF = Sheets("2").Cells(Sheets("2").Rows.Count, "D2").End(xlUp).row
lastRowM = Sheets("3").Cells(Sheets("3").Rows.Count, "A2").End(xlUp).row
This code uses cell references (A2, A2, D2, A2) where a column reference is required (A, A, D, A; or 1, 1, 3, 1)

I have started all over again:

Option Explicit


Sub Makro5()


Dim LastRow1 As Integer
Dim Found As Range
Dim Sheet1R As Long ' row
Dim Sheet3R As Long

Sheet3R = 2


Application.ScreenUpdating = False

LastRow1 = Sheets("1").Cells(Sheets("1").Rows.Count, "A").End(xlUp).Row

For Sheet1R = 2 To LastRow1

Set Found = Sheets("2").Range("A:A").Find(what:=Sheets("1").Cells(Sheet1R, "A"), lookat:=xlWhole, LookIn:=xlValues)
If Found Is Nothing Then
Sheets("3").Range(Sheets("3").Cells(Sheet3R, "A"), Sheets("3").Cells(Sheet3R, "B")).Value = _
Sheets("1").Range(Sheets("1").Cells(Sheet1R, "A"), Sheets("1").Cells(Sheet1R, "B")).Value
Sheet3R = Sheet3R + 1
End If

Next Sheet1R




End Sub

[Manonfire]

Hi,

Thank you for your reply.
I have one problem though:

"Set Found = Sheets("2").Range("A:A")."

This range needs to be columns "A" and "D" only. not "B" and "C".

I have tried this:

"Set Found = Sheets("2").Range("A","D")."
But it dosen't work.

Is there any way we can define the range only to consist of those two columns?

Thank you very much.