Convert a Find/Loop to an Array macro
On Sunday, April 17, 2016 at 4:42:35 AM UTC-7, Claus Busch wrote:
Hi Howard,
Am Sun, 17 Apr 2016 13:19:21 +0200 schrieb Claus Busch:
Sub Find_List_cRows()
the formula in the posted code does'nt work also.
Try it this way:
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
.Range("B1").Formula = "=IF(COUNTIF(C1:$C" & cRows & ",A1)0,A1,""missing"")"
.Range("B2:B" & aRows).Formula = _
"=IF(AND(COUNTIF($C$1:$C$" & cRows & ",A2)0,COUNTIF($B$1:B1,A2)<COUNTIF($C$1:$C$" _
& cRows & ",A2)),A2,""missing"")"
.Range("B1:B" & aRows).Value = .Range("B1:B" & aRows).Value
End With
Next 'wsh
Application.ScreenUpdating = True
End Sub
Regards
Claus B.
--
Hi Claus,
BINGO! That works as far as I can determine. I gave it about five different data sets and could not fool it.
Many thanks.
Howard
|