Counting names thru code

[Tom Jameson]

How does one count the number of times Tommy is present in
Column A (starting at A7) of Sheet 1 when I am in Sheet4?

I tried using:
With Worksheets("Sheets1
Selection.FormulaR1C1 = "=COUNTIF(Sheet1!RC[7]:end
(xlDown),""Tommy"")" but it did not work.

Anyone know how to correct my confused code above?

 

[Mike Tomasura]

Try this


Private Sub CommandButton1_Click()

For x = 7 To 1000

If Range("A" & x) = "Tommy" Then y = y + 1

Next x

Range("B1") = y

End Sub

 

[Bob Phillips]

or

Selection.FormulaR1C1 = "=COUNTIF(Sheet1!R7C1:R" & _
Worksheets("Sheet1").Cells(7, "A").End(xlDown).Row & _
"C1,""Tommy"")"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Try this


Private Sub CommandButton1_Click()

For x = 7 To 1000

If Range("A" & x) = "Tommy" Then y = y + 1

Next x

Range("B1") = y

End Sub

How does one count the number of times Tommy is present in
Column A (starting at A7) of Sheet 1 when I am in Sheet4?

I tried using:
With Worksheets("Sheets1
Selection.FormulaR1C1 = "=COUNTIF(Sheet1!RC[7]:end
(xlDown),""Tommy"")" but it did not work.

Anyone know how to correct my confused code above?