I have a need for a macro to do a simple match of figures in 2 sheets:

The macro should match Amt, REF and RT in sheets 1&2
Where there is match, should write in sheet2 corresponding S/N from
sheet1

Sheet1
S/N Amt REF RT

001 200 w1 john
002 100 e3 Pet
003 400 y7 Yep
004 600 z7 Pet


Sheet2
S/N Amt REF RT

001 400 y7 Yep
002 200 w1 john
003 100 e3 Pet
004 600 z7 Pet


Assumption
The number of row undetermined


Result format: the final result in sheet2 should appear as shown
below.

Sheet2
S/N Amt REF RT S/N(from sheet1)

001 400 y7 Yep 003
002 200 w1 john 001
003 100 e3 Pet 002
004 600 z7 Pet 004

HI

Try this:

Sub Compare()
Dim ShA As Worksheet
Dim ShB As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim f As Range

Set ShA = Worksheets("Sheet1")
Set ShB = Worksheets("Sheet2")

FirstRow = 3 'First row with data
LastRow = ShA.Range("A" & Rows.Count).End(xlUp).Row

For r = FirstRow To LastRow
Set f = ShB.Columns("B").Find(what:=ShA.Range("B" & r).Value,
LookIn:=xlValues, lookat:=xlWhole)
If Not f Is Nothing Then
If ShA.Range("C" & r) = f.Offset(0, 1).Value And ShA.Range("D" &
r).Value = f.Offset(0, 2).Value Then
f.Offset(0, 3) = ShA.Range("A" & r).Value
End If
End If
Set f = Nothing
Next
End Sub

Regards,
Per

"sylink" skrev i meddelelsen
...
I have a need for a macro to do a simple match of figures in 2 sheets:

The macro should match Amt, REF and RT in sheets 1&2
Where there is match, should write in sheet2 corresponding S/N from
sheet1

Sheet1
S/N Amt REF RT

001 200 w1 john
002 100 e3 Pet
003 400 y7 Yep
004 600 z7 Pet


Sheet2
S/N Amt REF RT

001 400 y7 Yep
002 200 w1 john
003 100 e3 Pet
004 600 z7 Pet


Assumption
The number of row undetermined


Result format: the final result in sheet2 should appear as shown
below.

Sheet2
S/N Amt REF RT S/N(from sheet1)

001 400 y7 Yep 003
002 200 w1 john 001
003 100 e3 Pet 002
004 600 z7 Pet 004

On Dec 10, 3:55*pm, "Per Jessen" wrote:
HI

Try this:

Sub Compare()
Dim ShA As Worksheet
Dim ShB As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim f As Range

Set ShA = Worksheets("Sheet1")
Set ShB = Worksheets("Sheet2")

FirstRow = 3 'First row with data
LastRow = ShA.Range("A" & Rows.Count).End(xlUp).Row

For r = FirstRow To LastRow
* * Set f = ShB.Columns("B").Find(what:=ShA.Range("B" & r).Value,
LookIn:=xlValues, lookat:=xlWhole)
* * If Not f Is Nothing Then
* * * * If ShA.Range("C" & r) = f.Offset(0, 1).Value And ShA.Range("D" &
r).Value = f.Offset(0, 2).Value Then
* * * * * * f.Offset(0, 3) = ShA.Range("A" & r).Value
* * * * End If
* * End If
* * Set f = Nothing
Next
End Sub

Regards,
Per

"sylink" skrev i ...

I have a need for a macro to do a simple match of figures in 2 sheets:

The macro should match Amt, REF and RT in sheets 1&2
Where there is match, should write in sheet2 corresponding S/N from
sheet1

Sheet1
S/N Amt REF *RT

001 200 w1 john
002 100 e3 Pet
003 400 y7 Yep
004 600 z7 Pet

Sheet2
S/N Amt REF *RT

001 400 y7 Yep
002 200 w1 john
003 100 e3 Pet
004 600 z7 Pet

Assumption
The number of row undetermined

Result format: the final result in sheet2 should appear as shown
below.

Sheet2
S/N Amt REF *RT S/N(from sheet1)

001 400 y7 Yep 003
002 200 w1 john 001
003 100 e3 Pet 002
004 600 z7 Pet 004

Thank you Per. I'll give a try.