Adding a formula to Excel VBA form save button

Question

I am building a form and coding the save button, I want certain cells to run a formula to auto calculate dates.

I am trying to type this: .Cells(lRow, 20).Formula = "=PEDate.Value-PSDate.Value"

I just want the value from PEDate box to be subtracted from PSDate, to calculate the number of days between these two dates.

The code I wrote is as follows:

Private Sub SBut_Click()

Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Projects")
lRow = ws.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row

With ws
    .Cells(lRow, 1).Value = SCBox.Value
    .Cells(lRow, 2).Value = SearchBox.Value
    .Cells(lRow, 3).Value = BHNumber.Value
    .Cells(lRow, 4).Value = SDate.Value
    .Cells(lRow, 5).Value = PSDate.Value
    .Cells(lRow, 6).Value = PEDate.Value
    .Cells(lRow, 7).Value = EDate.Value
    .Cells(lRow, 10).Value = COS.Value
    .Cells(lRow, 11).Value = Interviews.Value
    .Cells(lRow, 13).Value = Placement.Value
    .Cells(lRow, 15).Value = FTDate.Value
    .Cells(lRow, 17).Value = Rework.Value
    .Cells(lRow, 19).Value = PlacementConf.Value
    .Cells(lRow, 20).Formula = "=PEDate.Value-PSDate.Value"
End With
   

SCBox.Value = ""
SearchBox.Value = ""
BHNumber.Value = ""
SDate.Value = ""
PSDate.Value = ""
PEDate.Value = ""
EDate.Value = ""
COS.Value = ""
Interviews.Value = ""
Placement.Value = ""
FTDate.Value = ""
Rework.Value = ""
PlacementConf.Value = ""

Unload Me

Does anyone have recommendation on what to do?

Thank you in advance

Answer 1

Code:

x = [1 1 4 3]';
y = [4 1 5 6]';
t = table(x,y);
t.x(x<2)  = 2

Results:

t = 
    x    y
    _    _
    2    4
    2    1
    4    5
    3    6