Compare 2 sheets against 2 colums

[karaisland]

Hi
I have been reading old posts but couldn't find an answer to my prob.
I have two sheets "sales" and "salesold". They have the same fields.
What I want to do is compare "sales" to "salesold" and copy the
different rows into "compare" sheet.Comparing colums are "n" and "m"
ie.

- check value in "n" first if "salesold" doesn't have it then copy row
to "compare" from "sales".
- if "salesold" has it then check "m" column.If the "m"'s is the same
do nothing if different then
copy the row to "compare" from "sales".

Sheet "sales" has all the data which "salesold" can contain of
course.

Any direction ? Thank you very much in advance.

 

[Per Jessen]

Hi
I have been reading old posts but couldn't find an answer to my prob.
I have two sheets "sales" and "salesold". They have the same fields.
What I want to do is compare "sales" to "salesold" and copy the
different rows into "compare" sheet.Comparing colums are "n" and "m"
ie.

- check value in "n" first if "salesold" doesn't have it then copy row
to "compare" from "sales".
- if "salesold" has it then check "m" column.If the "m"'s is the same
do nothing if different then
  copy the row to "compare" from "sales".

Sheet "sales" has all the data which "salesold" can contain of
course.

Any direction ?  Thank you very much in advance.

Hi

Try this code

Sub Compare_Sales()

Dim TargetRange As Range
Dim SearchRange As Range
Dim Search2Range As Range
Dim shSales As Variant
Dim shOSales As Variant
Dim shCompare As Variant
Dim fSales As Variant
Dim dCell As String

Set shSales = Sheets("Sales")
Set shOSales = Sheets("Salesold")
Set shCompare = Sheets("Compare")

shSales.Select
Set TargetRange = Range("N2", Range("N2").End(xlDown)) 'Headings in
row # 1

shOSales.Select
Set SearchRange = Range("N2", Range("N2").End(xlDown))

dCell = "A2"

For Each c In TargetRange
Set fSales = SearchRange.Find(what:=c.Value)
If fSales Is Nothing Then
c.EntireRow.Copy shCompare.Range(dCell)
dCell = shCompare.Range(dCell).Offset(1, 0).Address
Else
If c.Offset(0, -1).Value <> fSales.Offset(0, -1).Value Then
c.EntireRow.Copy shCompare.Range(dCell)
dCell = shCompare.Range(dCell).Offset(1, 0).Address
End If
End If
Next
End Sub

Regards,
Per

 

[karaisland]

Dear Per Jessen

You are definitely not from heaven but this doesn't mean you will
remain here for ever.

Thank you very much.