Search code examples
excelvbatextformulacell

Excel VBA to input formula followed by text into cell or cell formula

I have the count working fine as below:

Sheet2.Range("A1").End(xlDown).Offset(1, 3).FormulaR1C1 = "=COUNTA(R2C:R[-1]C)"

I want the cell to have the count followed by the word "Schemes"

I have tried

Sheet2.Range("A1").End(xlDown).Offset(1, 3).FormulaR1C1 = "=COUNTA(R2C:R[-1]C)"  & "Schemes"

but fails. I am not sure about the quototation marks. I also tried using + and AND but also not working.

Solution

  • Please, try it in this way:

    Sheet2.Range("A1").End(xlDown).Offset(1, 3).FormulaR1C1 = "=COUNTA(R2C:R[-1]C)&"" Schemes"""

    The concatenation character (&) must be inside the formula string and any double quotes must be doubled when the code writes the formula. I also thought that a space must be let between the formula result and the ending string...