Formula entered in VBA resulting in #Name?

As per the below snippet of code, I am entering a formula, in VBA, to a range of cells. The procedure successfully adds the formula but even though I recalculate afterwards the cells just show '#Name?'???

The formula is correct and if I manually edit the cell and then press 'enter' then the formula will calculate.

Any ideas how I can get the formulas to calculate once I've entered the code?

Many thanks,

Adrian

mrange.Cells(m, 1).Offset(, 14).FormulaR1C1 = "=if(vlookup(RC1, '" & NIsheetname & "'!a:o, 15, false)= """","""",(vlookup(RC1, '" & NIsheetname & "'!a:o, 15, false)))"

Hello Kobayashi,

To cause Excel to calculate via code...

To calculate____________Follow this example
All open workbooks ............... Application.Calculate (or just Calculate)
A specific worksheet................Worksheets(1).Calculate
A specified range ................... Worksheets(1).Rows(2).Calculate

Sincerely,
Leith Ross

I think the formula entered is treated as a string and so it does not become a formula untill you manually enter the cell and come out, after which it calculates properly.

After your macro operation, you see the formula in the cell, right, and not the value. What is the actual formula string.

- Mangesh

Thanks for both of your replies, however:

Leith,

I already add code to (re) calculate the worksheet, which doesn't make any difference. Indeed, even if I calculate the sheet manually afterwards there is no effect.

Mangesh,

This is the forumala that is returned to the cells:

=IF(VLOOKUP($A23, '[Book3]New Items'!a:o, 15, FALSE)= "","",(VLOOKUP($A23, '[Book3]New Items'!a:o, 15, FALSE)))

However, what shows in the cells is '#NAME?'. If I then edit the cell but do not make any changes and just exit by pressing the 'enter' key then the cell will evaluate correctly?

Thanks,

Adrian