Excel VBA to paste value and not formula

[bquek]

I am adding the columns helper to my report. Currently the VBA paste the excel formula and I want to change it to paste value instead. How do I change the excel formula to VBA language (bolded lines below)? Appreciate any tips or help or reference.

Sub Test_ACode()

Dim DateName$, ReportDate$
Dim x As Long

DateName = InputBox("Enter Report Cut Off Date MM/dd/yyyy")
ReportDate = DateName

'To add column helpers
Range("T1") = "Dist_Name"
Range("U1") = "ESC Region"
Range("V1") = "RptDate"
Range("W1") = "Age"
Range("X1") = "C_Lep"
Range("Y1") = "C_Imm"
Range("Z1") = "C_Ecodis"
Range("AA1") = "C-PK_Foster"

For Each cell In Range("A2:A" & Range("A65536").End(xlUp).Row)

'Identify cell value not equal blank
If cell.Value <> "" Then

x = 0


'Paste value when cell value does not equal blank
'but want to change below excel formulas to vba language except ReportDate

cell.Offset(x, 19).FormulaR1C1 = "=Vlookup(RC[-4],Table1,2)"
cell.Offset(x, 20).FormulaR1C1 = "=Vlookup(RC[-5],Table1,5)"
cell.Offset(x, 21).FormulaR1C1 = ReportDate
cell.Offset(x, 22).FormulaR1C1 = "=ROUNDDOWN(DATEDIF(RC[-16],RC[-1],""y""),0)"
cell.Offset(x, 23).FormulaR1C1 = "=IF(RC[-14]=1,1,"""")"
cell.Offset(x, 24).FormulaR1C1 = "=IF(Rc[-10]=1,1,"""")"
cell.Offset(x, 25).FormulaR1C1 = "=IF(OR(rc[-17]=1,rc[-17]=2,rc[-17]=99),1,"""")"
cell.Offset(x, 26).FormulaR1C1 = "=IF(OR(rc[-13]=1,rc[-13]=2),1,"""")"

x = x + 1

End If
Next

End Sub

 

[ParamRay]

.
.

Perhaps this will get you on the right track:

Option Explicit

Sub test_code()

    Dim varReportDate As Variant
    Dim rngLoopCell As Range
    
GetDate:
    varReportDate = Application.InputBox( _
        Prompt:="Enter report cut-off date (mm/dd/yyyy):", _
        Default:=Date, _
        Type:=2)    'String
    If varReportDate = False Then Exit Sub
    On Error GoTo GetDate
    varReportDate = CDate(varReportDate)
    On Error GoTo 0
    
    Cells(1, "t").Resize(1, 8).Value = Array( _
        "Dist_Name", _
        "ESC Region", _
        "RptDate", _
        "Age", _
        "C_Lep", _
        "C_Imm", _
        "C_Ecodis", _
        "C-PK_Foster")
        
    For Each rngLoopCell In Range(Cells(2, "a"), Cells(Rows.Count, "a").End(xlUp)).Cells
        With rngLoopCell
            If .Value <> vbNullString Then
                Cells(.Row, "t").Value = _
                    Evaluate("=Vlookup(p" & .Row & ",Table1,2)")
                Cells(.Row, "u").Value = _
                    Evaluate("=Vlookup(o" & .Row & ",Table1,5)")
                Cells(.Row, "v").Value = _
                    varReportDate
                Cells(.Row, "w").Value = _
                    Evaluate("=ROUNDDOWN(DATEDIF(f" & .Row & ",v" & .Row & ",""y""),0)")
                Cells(.Row, "x").Value = _
                    Evaluate("=IF(j" & .Row & "=1,1,"""")")
                Cells(.Row, "y").Value = _
                    Evaluate("=IF(0" & .Row & "=1,1,"""")")
                Cells(.Row, "z").Value = _
                    Evaluate("=IF(OR(i" & .Row & "=1,i" & .Row & "=2,i" & .Row & "=99),1,"""")")
                Cells(.Row, "aa").Value = _
                    Evaluate("=IF(OR(n" & .Row & "=1,n" & .Row & "=2),1,"""")")
            End If
        End With
    Next rngLoopCell

End Sub

 

[ParamRay]

.
.

If the above code isn't returning the expected values, try removing the surrounding Evaluate( ... ) functions to see what is actually being calculated...

 

[bquek]

Thank you for the coding solutions. Did do a few changes per suggestion and it works smoothly. Problem solved.