G
Guest
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
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