Set Range object problem in occurrence count?
Posted by Joe Was on June 01, 2001 7:54 AM
Below is the code to find unique names and the number of times each has occurred. The data is on one sheet the results on another.
The problem code is in the bottom half, count occurrences on Sheet2 & list count on Sheet1. (Parts are commented!) If any one has a solution, thank's.
'Finds all the unique names and count the number of times they are listed.
'Data is on Sheet2, Results are listed on Sheet1.
Dim rng As Range
'Find unique names on Sheet2 & list on Sheet1.
Application.ScreenUpdating = False
With Intersect(Columns(8), ActiveSheet.UsedRange)
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
'Sort unique names on sheet1.
'Code is fine down to here!
Set tallyRange = Worksheets("Sheet2").Range(Range("I1"), Range("I1").End(xlDown)).Offset(0, 1)
Set fillRange = Worksheets("Sheet1").Range("J1")
'Count occurrences of names on Sheet2.
.Formula = "=CountIf(" & Intersect(Columns(8), ActiveSheet.UsedRange).Address & ",I1)"
'List the number of times each unique name occurred on Sheet2, next to the list on sheet1.
Application.CutCopyMode = False
Application.ScreenUpdating = True