Convert a Find/Loop to an Array macro
On Sunday, April 17, 2016 at 4:19:27 AM UTC-7, Claus Busch wrote:
Hi Howard,
Am Sun, 17 Apr 2016 04:05:10 -0700 (PDT) schrieb L. Howard:
I may not understand the duplicates situation you mention.
if you check the output you will find out that it is not correct. The
duplicates are not correctly handled.
It is easier to do it with a formula.
Try:
Sub Find_List_cRows()
Dim aRows As Long, cRows As Long
Dim varData As Variant, varOut() As Variant
Dim wsh As Worksheet
Application.ScreenUpdating = False
For Each wsh In Worksheets
With wsh
cRows = .Cells(.Rows.Count, "C").End(xlUp).Row
aRows = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("B1:B" & aRows)
.Formula = "=IF(COUNTIF(C1:$C" & cRows &
",A1)0,A1,""missing"")"
.Value = .Value
End With
End With
Next 'wsh
Application.ScreenUpdating = True
End Sub
Regards
Claus B.
Hi Claus,
Yes, you are correct. I changed the data to something easier to read and it is incorrect as you say.
I'll give the formula version a try.
Howard
|