MrExcel.com - Your One Stop for Excel Tips & Solutions

Count on one sheet list on other?

Posted by Joe Was on June 04, 2001 6:52 AM

On sheet2 is a column of names. On sheet1 the sorted list of unique names get posted. Every thing I try to get the number of times each name on sheet2 is listed on sheet1 fails?

'Problem code!

Set tallyRange = Worksheets("Sheet2").Range(Range("I1"), Range("I1").End(xlDown)).Offset(0, 1)
Set fillRange = Worksheets("Sheet1").Range("J1")
With Worksheets("Sheet2").Range("J1")

'Count occurrences of names on Sheet2.

.Formula = "=CountIf(" & Intersect(Columns(8), ActiveSheet.UsedRange).Address & ",I1)"
.AutoFill Destination:=Range(fillRange.Address)
End With
With tallyRange

'List the number of times each unique name occurred on Sheet2, next to the list on sheet1.

.Copy Destination:=Range(fillRange.Address)
.PasteSpecial (xlValues)
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
Sheets("Sheet1").Select
Range("A1").Select
End Sub

Any ideas with this form button code? Thank's. JSW

Posted by cpod on June 04, 2001 12:01 PM

How about just:


'Count occurrences of names on Sheet2.
.Range("j1").Formula = "=CountIf(Sheet2!" & Intersect(Sheet2.Columns(8), Sheet2.UsedRange).Address & ",I1)"
.Range("j1:j" & .Range("h1").End(xlDown).Row).FillDown

Posted by cpod on June 04, 2001 12:19 PM

Forgot the with statement:

With Worksheets("Sheet1")
'Count occurrences of names on Sheet2.
.Range("j1").Formula = "=CountIf(Sheet2!" & Intersect(Sheet2.Columns(8), Sheet2.UsedRange).Address & ",I1)"
.Range("j1:j" & .Range("h1").End(xlDown).Row).FillDown
End With

Posted by Joe Was on June 04, 2001 2:00 PM

Thank's Cpod

I am out of the office and checked this board with my cellphone. I will check the code out Tuesday. Thank's. JSW

Posted by Joe Was on June 05, 2001 6:45 AM

Cpod: Did not work, this is the whole code!

This is the whole code.

Sub Find_Names()
'Finds all the unique names and count the number of times they are listed.
'Data is on Sheet2, Results are listed on Sheet1.
Dim tallyRange As Range
Dim fillRange As Range
'Find unique names on Sheet2 & list on Sheet1.
Application.ScreenUpdating = False
With Intersect(Columns(8), ActiveSheet.UsedRange)
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
.SpecialCells(xlCellTypeVisible).Copy Destination:=Worksheets("Sheet1").Range("I1")
ActiveSheet.ShowAllData
End With
'Sort unique names on sheet1.
Sheets("Sheet1").Select
Columns(9).Sort Key1:=Range("I1")
'Code is fine down to here!

'Problem code!
Set tallyRange = Worksheets("Sheet2").Range(Range("I1"), Range("I1").End(xlDown)).Offset(0, 1)
Set fillRange = Worksheets("Sheet1").Range("J1")
With Worksheets("Sheet1")
'Count occurrences of names on Sheet2.
.Range("j1").Formula = "=CountIf(Sheet2!" & Intersect(Sheet2.Columns(8), Sheet2.UsedRange).Address & ",I1)"
.Range("j1:j" & .Range("h1").End(xlDown).Row).FillDown
End With
With tallyRange
'List the number of times each unique name occurred on Sheet2, next to the list on sheet1.
.Copy Destination:=Range(fillRange.Address)
.PasteSpecial (xlValues)
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
Sheets("Sheet1").Select
Range("A1").Select
End Sub

Excel VB is not liking the Set statements! (tallyRange & fillRange)

Any ideas? JSW