I need to add an additional item to filter code?
Posted by Joe Was on June 14, 2001 9:01 AM
The problem is in the last full module! The rest of the code works well, only the SUM (SumIf), returns wrong or missing values.
I am not certain that SumIf is the right way to sum in this case or if I should re-run the filter?
The code is commented, and works this way; A random list of names is filtered to give a unique list of names on a different sheet. The filtered list is sorted. The number of times each name appears on the original sheet is indicated next to the name on the sorted list.
The problem is; The random list also contains values, which must be totaled next to the count on the sorted list. The code runs and makes the sorted list and count but fails to sum the values? (Only the first Sum row is right, the other sum rows are wrong or missing?)
I think the problem may be in the "Criteria" for the "SumIf?"
Any help will be appreciated!
'Finds all the unique names, count the number of times each name is listed and give the total value of each name.
'Data is on Sheet2: Names(G), Values(I), Results are listed on Sheet3: Names(H), Count(I) & Values(J).
Application.ScreenUpdating = False
'Find unique names on Sheet2(G), col(7) & list on Sheet3(H).
With Intersect(Columns(7), ActiveSheet.UsedRange)
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
'Sort unique names on sheet3(H).
'Count occurrences of names on Sheet2(G), col(7).
'List the number of times each unique name occurred on Sheet2(G), next to the list on sheet3(I).
.Range("I1").Formula = "=CountIf(Sheet2!" & Intersect(Sheet2.Columns(7), Sheet2.UsedRange).Address & ",H1)"
.Range("I1:I" & .Range("H1").End(xlDown).Row).FillDown
'Sum values by name on sheet2(I).
'List the sum of values for each unique name occurred on Sheet2: Name(G), Value(I),
'on sheet3: Name(H) & Value(J).
.Range("J1").Formula = "=SumIf(Sheet2!" & Intersect(Sheet2.Columns(7), Sheet2.UsedRange).Address & ",H1,Sheet2!I1)"
.Range("J1:J" & .Range("H1").End(xlDown).Row).FillDown
Application.CutCopyMode = False
Application.ScreenUpdating = True