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?

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



"Tom Jameson" wrote in message
...
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?

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)

"Mike Tomasura" wrote in message
...
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



"Tom Jameson" wrote in message
...
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?