| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with formulae in VBA
Thanks Bob
"John" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I have a problem with a formulae. | Excel Discussion (Misc queries) | |||
| Formulae: Paste value formulae after doing an average operation | Excel Discussion (Misc queries) | |||
| Array formulae - problem | Excel Discussion (Misc queries) | |||
| Help with Formulae please! | Excel Discussion (Misc queries) | |||
| Searching TEXT in formulae, rather than results of formulae | Excel Worksheet Functions | |||
Hybrid Mode
