Cell shows #NAME? after VBA inputs formula

1
votes
Cells(intRow, 2 + j).Formula = "=SUM(C" & intRow & ":" & colName & intRow & ")"

This is the code I am using to put a basic SUM-functionality into the cells at the end of certain rows. Upon entering this formula in the cell, the cell shows "#NAME?". If i click into the formula and then press "Enter", the formula works as intended (the formula input is also correct).

Is there a way to make VBA update these cells automatically, so the formula works as soon as it's entered into the cell by VBA?

Edit: Example values for the variables are:

intRow = 5
j = 7 (Column G)
colName = H (refers to j + 1)

So the finished formula in cell K5 would be:

=SUM(C5:J5)
2
vbaexcelexcel-formula
What does "=SUM(C" & intRow & ":" & colName & intRow & ")" evaluate to? You need to post a minimal reproducible example. - John Coleman
Do you have calculation on automatic? Is the cell format correct initially (i.e. General) - Jpad Solutions
So, we are not going to be able to answer this without knowing the values of the variables at the time of the error. You need to show more of your code. We need to see how the variables are declared and what value is being assigned to them. - Scott Craner
For clarification: The error is thrown in the cell (not when executing the code), until I click inside the formula bar (which displays the correct formula) and press "Enter". After that it works as intended. - riskypenguin
I've had something similar with Excel 2016 64bit - UDF's wouldn't calculate despite being set to volatile, automatic calculation, F9 and selection.calculate. The only solution I found was to search for "=" and replace it with "=" this actually made it recalculate. I suspect there's a bug in Excel's routine for spotting what needs recalculating - Harassed Dad

2 Answers

0
votes

Solved: Using 

.FormulaR1C1

does not cause the same error.

Cells(intRow, 2 + j).FormulaR1C1 = "=SUM(RC[" & -(j - 1) & "]:RC[" & (-1) & "])"

0
votes

I just had the same problem, for a different reason, but I think it may help someone else :

It was due to a problem of localization (language)

I'm a French user and I was trying the following code :

For Each cel In selectedRange.Cells cel.FormulaR1C1 = "=SI(R[0]C[-1]=1,1,0)" Next cel

Where SI corresponds to the formula IF in English.

The problem is that FormulaR1C1 accept only English Formula: so to fix it I had to use :

For Each cel In selectedRange.Cells cel.FormulaR1C1 = "=IF(R[0]C[-1]=1,1,0)" Next cel

Otherwise I can also use my French formula thanks to FormulaR1C1Local such as :

For Each cel In selectedRange.Cells cel.FormulaR1C1Local = "=SI(L(0)C(1)=1;1;0)" Next cel

(note that L/C are for french words : Ligne/Colonne, that I needed to use the separator ; , that brackets are replaced by parenthesis in the Excel's French world ..)