ActiveCell.FormulaR1C1 = "=COUNTA('" & MySheet & "'!C1)-1"

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"John" wrote in message
...
Hi,

I've Recorded Excel placing a formula in a cell and amended it to pick up
a
sheetname reference in the formula.

In XL, the formula is
=COUNTA('211106'!$A:$A)-1
When i record the formula i get
ActiveCell.FormulaR1C1 = "=COUNTA('211106'!C1)-1"
I changed the hardcode 221106 to be a reference to a variable called
MySheet,
therefore the formula in VBA now reads:
ActiveCell.FormulaR1C1 = "=COUNTA("" ' " & MySheet & " '!C1)-1"""
When i step through this i get the error:
Runtime error 1004 Application-defined or object-defined error
If it was a mistake with the Syntax then before i could run it i would get
the error, so somewhat naively i would assume the problem is with the
concatenation.

Appreciate if anyone has a solution to this please.

Thanks

John