Extract duplicates from two list to another sheet

[J.W. Aldridge]

I have a list of numbers in column B. I have another list in column I
on sheet1.

Any codes to extract any duplicates (numbers found) to column C in
sheet2 using code?
Formulas takes too long and locks up workbook.


Thanx

 

[meh2030]

I have a list of numbers in column B. I have another list in column I
on sheet1.

Any codes to extract any duplicates (numbers found) to column C in
sheet2 using code?
Formulas takes too long and locks up workbook.

Thanx

I haven't really done much of this (and there is likely a more
efficient method), but off the top of my head I know that you can load
both sets of ranges into arrays and loop through the arrays.

Sub Macro1()
Dim rngRange1 As Range
Dim rngRange2 As Range

Dim rngRange1Cell As Range
Dim rngRange2Cell As Range

Dim varRange1Val As Variant
Dim varRange2Val As Variant

Set rngRange1 = Worksheets(1).Range("a2:a4")
Set rngRange2 = Worksheets(1).Range("b2:b4")

For Each rngRange1Cell In rngRange1.Cells
varRange1Val = rngRange1Cell.Value
For Each rngRange2Cell In rngRange2.Cells
varRange2Val = rngRange2Cell.Value
If varRange1Val = varRange2Val Then
'Extract
End If
Next
Next

End Sub

Matt

 

[meh2030]

I haven't really done much of this (and there is likely a more
efficient method), but off the top of my head I know that you can load
both sets of ranges into arrays and loop through the arrays.

Sub Macro1()
Dim rngRange1 As Range
Dim rngRange2 As Range

Dim rngRange1Cell As Range
Dim rngRange2Cell As Range

Dim varRange1Val As Variant
Dim varRange2Val As Variant

Set rngRange1 = Worksheets(1).Range("a2:a4")
Set rngRange2 = Worksheets(1).Range("b2:b4")

For Each rngRange1Cell In rngRange1.Cells
    varRange1Val = rngRange1Cell.Value
    For Each rngRange2Cell In rngRange2.Cells
        varRange2Val = rngRange2Cell.Value
        If varRange1Val = varRange2Val Then
            'Extract
        End If
    Next
Next

End Sub

Matt

I know I said "arrays" but I meant range object(s). However, you
could also create arrays to do this same process.

Matt