On 26 Apr., 17:32, karaisl...@gmail.com wrote:
> 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
|
Similar Threads
