Adding a formula to Excel VBA form save button-CodePudding

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

CodePudding user response：

If you want it to be a formula (that recalculates on value changes) you need to reference the cells where you have written those values to not the values of the textboxes:

.Cells(lRow, 20).Formula = "=" & .Cells(lRow, 6).Address & "-" & .Cells(lRow, 5).Address

If you only want to calculate the values once and write them as constant values you can do that by:

.Cells(lRow, 20).Value = PEDate.Value - PSDate.Value

The problem is if you enter dates in textboxes they are not dates but strings. So you need to split them into day, month and year and use DateSerial() to build a real numeric date with it (so you can calculate with that date).

.Cells(lRow, 20).Value = ConvertDDMMYYYYToDate(PEDate.Value) - ConvertDDMMYYYYToDate(PSDate.Value)

Public Function ConvertDDMMYYYYToDate(ByVal DateString As String) As Date
    Dim DateSplit() As String
    DateSplit = Split(DateString, "/")
    
    Dim RetVal As Date
    If UBound(DateSplit) = 2 Then
        ' build a real numeric date
        RetVal = DateSerial(DateSplit(2), DateSplit(1), DateSplit(0))
        
        ' test if the string was a real date
        If Format$(RetVal, "DD\/MM\/YYYY") = DateString Then
            ConvertDDMMYYYYToDate = RetVal
        End If
    End If
End Function