How to pass a formula from one workbook to another so it displays results?

Question

I want to transfer formulas from one workbook to another.

Once the formulas are transferred, they show up in the cells as formulas, instead of the results of their calculations.

The workbooks and sheets:

Set wb = Workbooks("Main.xlsm")
Set wsh = wb.Worksheets("Positions")
Set wbF = Workbooks("Formulas.xlsx")
Set wshF = wbF.Worksheets("Sheet2")

The formulas worksheet looks like this:

The code is this:

Sub EnterFormulasFromFormulasSheet()
    ' This sub enters all the formulas into all the rows after the entries were done in the Form Options2Entry
    ' there is a Totals row thatis the last row of the range, thereforeneed to find last row,
    ' insert a blank row there and then do the entries intothat newblank row
    
    Set wb = Workbooks("Main.xlsm")
    Set wsh = wb.Worksheets("Positions")
    Set wbF = Workbooks("Formulas.xlsx")
    Set wshF = wbF.Worksheets("Sheet2")
    
    With wsh
       ' Last Row Number is ir because there is a Total row which is the last row and the working row is inserted above it
        ' this isdone in the FormEntry form module
       RealLastRow = Range("A" & Rows.count).End(xlUp).Row
       'Range("A" & RealLastRow).EntireRow.Insert ' The actual insertion takes place in the FormEnry form
       ir = RealLastRow - 1 ' after the insertion last (Total) row moves down,
                                       'but variable RealLastRow stays where the now inserted row is
                                       'this applies to the form because that's where the row is inserted, no insertion here though
    End With

   
   ' In order to make the worksheet dynamic,all the cells must contain formula,not just calculated results
   
  Set rngF = wshF.Range("A1:C51")
  Dim count As Long
  Dim colF As String
  Dim FormulaString As String

  For count = 1 To 51
    If wshF.Cells(count, "C") <> "" Then
        colF = wshF.Cells(count, "A").Value
        FormulaString = wshF.Cells(count, "C")
        FormulaString = Replace(FormulaString, Chr(126), Chr(34)) 'this replaces every instance of the tilde with a double quote.
        FormulaString = Replace(FormulaString, "|", ir) ' this replaces | with ir
        wsh.Cells(ir, colF).Formula = FormulaString
     End If
  Next count
End Sub

The code produces this result in the Main workbook:

When I go through the code step by step the final step:

wsh.Cells(ir, colF).Formula = FormulaString

shows FormulaString formatted properly like this:

"=IF($C9=0,"",$I9/$H9)"

I tried using double quotes at beginning and end, but it made no difference.

Answer 1

Try

Dim strFormula As String

strFormula = "=ROUND(A3,0)"

ThisWorkbook.Worksheets(Working).Range("A4").Formula = strFormula