Selected Answer
Hi style36
I like this site as I can both learn and teach.
I must confess that I cannot see why you would use a macro to insert a fomular then using the same macro replace the formula with its values.
I woul simple use the macro to calculate and store the result.
I do have code to insert a fomula.
'errcnt = 1
For cls = 7 To intLastRow
If Cells(cls, StatusColn) = "" Or Cells(cls, StatusColn) = "Pending" Or Cells(cls, StatusColn) = "To Script" Then
Cells(cls, StatusColn) = "=IF(" & TCIDcola & cls & "<>"""",IF(" & ExpectResultCola & cls & "<> """",""Pending"",""To Script""),"""")"
End If
This is used to report project status which updates from "To Script" to "Pending" as test expected results are entered not that anyone cares.
However, it is just a sample from a larger process which is one of many.
So please describe what your problem is rather than suggest a method to a solution so that we can all learn or show the various ways it can be solved.
Here you go this code was pinched from a response to someone elses question
Sub UpdateValue()
' 16 Jul 2019
Dim Rl As Long
Dim R As Long
' re-define workbook if the workbook isn't the one containing the code
' replace "Sheet1" with tab name
With ThisWorkbook.Worksheets("Sheet1")
Rl = .Cells(.Rows.Count, "B").End(xlUp).Row
' row 2 is the first row to sum up assume a heading row to ignore
For R = 2 To Rl
' write the result to column B
.Cells(R, "D").Value = .Cells(R, "B") 'Put your code here
'example to myltiply by 2 .Cells(R, "D").Value = .Cells(R, "B")*2
Next R
End With
End Sub