Excel does not calculate formula automatically, that is inserted via VBA

1
votes

I am creating a formula via vba and .FormulaR1C1 but Excel does not seem to realize it is a formula. It shows the correct formula within the formula bar and a Reference Error in the cell itself.

I tried a test sheet with a simple formula

    .Cells(i, recalcCol).FormulaR1C1 = "=RC(-1)"

The cell format is set at general. Inserting .NumberFormat = "general" at any point before or after does not change the behavior. It is definitely not a text cell.

I can enter the cell with double-click or F2 and then hit Enter and Excel removes the error and displays the correct result. But doing that for all changed cells is not possible.

I also tried recalculating the sheet, but no change there either.

Any hints?

Thx Kaz

1
vbaexcel
"=RC[-1]" square brackets – Slai
Damn ... that easy ... thats what happens when translating from german to english formulas. Thx a lot! You want the correct answer vote? – Kazschuri

1 Answers

1
votes

As mentioned in the comments, =RC[-1] is the correct formula.

However, in order to see the formula in RC format yourself, do the following:

  • Select the cell with the formula
  • Run the code below
  • Check the immediate window
Option Explicit

Public Sub TestMe()

    Debug.Print Selection.FormulaR1C1
    Debug.Print Selection.Formula
    Debug.Print Selection.FormulaLocal

End Sub

Concerning German to English, while you are using .FormulaR1C1 or .Formula you should not worry about it, Excel takes care of it. You should be worried only if you pass the formula as a string through .FormulaLocal.

This is the result in the immediate window from the Sub above, using a simple SUM() formula:

=RC[-1]+SUM(5,5)
=M10+SUM(5,5)
=M10+SUMME(5;5)