Matching values in a range

[teodormircea]

Hello
I need some help for a macros solution. I need to match a range af value using only 2 columns like criteria(here serial number) and the copy the results in 2 separates sheets Match and No Match.I join an exemple


:banghead:

[teodormircea]

Nobody ???

[figment]

i am sure that there are a lot of ways to impove this code but lunch is only so long here you go

Sub testing()
Dim a As Long, b As Long, c As Long, d As Long, match As Long
a = Worksheets("Sheet1").Range("A2").End(xlDown).Row
ReDim lList(1 To a, 1 To 7) As Variant
lList = Range("A2:G" & a)
b = Worksheets("Sheet1").Range("H2").End(xlDown).Row
ReDim rList(1 To b, 1 To 8) As Variant
rList = Range("H2:O" & b)
'find maches
match = 2
For c = 1 To a - 1
For d = 1 To b - 1
If lList(c, 1) = rList(d, 2) Then
With Worksheets("MATCH")
.Range("A" & match) = lList(c, 1)
.Range("b" & match) = lList(c, 2)
.Range("c" & match) = lList(c, 3)
.Range("d" & match) = lList(c, 4)
.Range("e" & match) = lList(c, 5)
.Range("f" & match) = lList(c, 6)
.Range("g" & match) = lList(c, 7)
.Range("h" & match) = "MATCH"
.Range("i" & match) = rList(d, 1)
.Range("j" & match) = rList(d, 2)
.Range("k" & match) = rList(d, 3)
.Range("l" & match) = rList(d, 4)
.Range("m" & match) = rList(d, 5)
.Range("n" & match) = rList(d, 6)
.Range("o" & match) = rList(d, 7)
.Range("p" & match) = rList(d, 8)
match = match + 1
lList(c, 1) = ""
rList(d, 2) = ""
End With
End If
Next
Next
'list no matches
match = 2
For c = 1 To a - 1
If lList(c, 1) <> "" Then
With Worksheets("NO MATCH")
.Range("A" & match) = lList(c, 1)
.Range("b" & match) = lList(c, 2)
.Range("c" & match) = lList(c, 3)
.Range("d" & match) = lList(c, 4)
.Range("e" & match) = lList(c, 5)
.Range("f" & match) = lList(c, 6)
.Range("g" & match) = lList(c, 7)
.Range("h" & match) = "NO MATCH"
match = match + 1
End With
End If
Next
For d = 1 To b - 1
If rList(d, 2) <> "" Then
With Worksheets("NO MATCH")
.Range("h" & match) = "NO MATCH"
.Range("i" & match) = rList(d, 1)
.Range("j" & match) = rList(d, 2)
.Range("k" & match) = rList(d, 3)
.Range("l" & match) = rList(d, 4)
.Range("m" & match) = rList(d, 5)
.Range("n" & match) = rList(d, 6)
.Range("o" & match) = rList(d, 7)
.Range("p" & match) = rList(d, 8)
match = match + 1
End With
End If
Next
End Sub

[teodormircea]

If i use integer values is a mess, and worst some times i use chars also

[teodormircea]

It will be more simple if i can choose the 2 ranges and the criteria using an userform

[teodormircea]

: pray2: : pray2: : pray2: : pray2: : pray2: : pray2:

[teodormircea]

:banghead::banghead:: pray2:: pray2:: pray2:: pray2: