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

On Feb 20, 12:16*pm, "J.W. Aldridge"
wrote:
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

On Feb 20, 12:30*pm, wrote:
On Feb 20, 12:16*pm, "J.W. Aldridge"
wrote:

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

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

Matt