[SOLVED] Excel VBA 2013 Customised Sub only Pasted not executed in cell with use of FormulaR1C1

Excel VBA 2013 Customised Sub only Pasted not executed in cell with use of FormulaR1C1

I've written a specific sub for formatting data from another cell.

Sub Macro3()
    '>> First record to get the original code in Testing.xlsm <<
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "=fG_ReFormattingInvoiceNumber(RC[-3],3)"
    Range("H5").Select
    ActiveWorkbook.Save

    '>>> I wrote in my application this line => ReInvoicingWBook.Sheets _
    '    (G_sheetNameREINVOICinG).Range("U5").FormulaR1C1 = _
    '    "=fG_ReFormattingInvoiceNumber(RC[-19],3)"
End Sub

The problem is that ReInvoicingWBook.Sheets(G_sheetNameREINVOICinG).Range("U5") Cell is just filled with the formula but it is not executed.

ReInvoicingWBook is a file opened by the Excel application I'm developing and writing inside it.

Yet, calling to fG_ReFormattingInvoiceNumber() works nice in the original file Testing.xlsm/ Sub Macro3 where I captured VBA lines.

The 1st thing I see is the fact that ReInvoicingWBook is a pointer to a file XLSM that is monitored by my application. Perhaps, it's a reason...

Any solution or help from you are appreciated.

Solution

You can force a calculation of cells by using .Calculate.

Sub Macro3()
    Range("F5").FormulaR1C1 = "=fG_ReFormattingInvoiceNumber(RC[-3],3)"
    Range("F5").Calculate
    ActiveWorkbook.Save
End Sub

As a general rule, always use Option Explicit, avoid using .Select and .Activate. Also, fully qualify your ranges (which is something I have not done in the example above because I have no context).