Countif a Name occures in a column

[Tempy]

Hi all,

I need to count the number of time a name appears in a column and then
save the amount in a cell.

I would like to do this with code as i manipulate the spreadsheet.
I have done it with a countif formular in the cell, nut must one do this
? or is it better to store the total as a variable and then past the
variable into the cell, it is the latter that i am not sure how to do.

Thanks in advance

Tempy

 

[Mike Fogleman]

Sub CountName ()
Dim numname as Long
Dim namerng as Range

Set namerng as Sheets("Sheet1").Range("A1:A100") 'where your list of
names are located
numname = Application.WorksheetFunction.Countif("Name", namerng)
Sheets("Sheet1").Range("B1"). Value = numname 'puts answer on sheet
End Sub

Mike F

 

[Guest]

Sub CountNames()
MyName = "Alan"
Set Myrng = Range("A1:A10000")
NameCount = Application.CountIf(Myrng, MyName)
MsgBox NameCount
Range("B1") = Application.CountIf(Myrng, MyName) ' or Range("B1")=NameCount
End Sub

 

[Tempy]

Thanks all for the input.

Tempy

 

[Jim May]

Sub Foo()
cnum = Application.CountIf(Range("C:C"), "*" & Test & "*")
Range("A1").Value = cnum
End Sub

 

[Tempy]

Could you please give me some more advice on this topic.

I need to look for 19 different names and put the totals in a column. Is
it possible to have a list of names, perhaps in my Personal.xls
workbook, and with the code find the results for one name, paste it and
then get the next name, until the end of the list ?


Tempy

 

[Don Guillett]

Name your list of names and do a loop or just use a vlookup formula

for each name in [mylistofnames]
code
next