My formula to add 1 year to a date in an adjacent cell works, but not when I populate that adjacent-CodePudding

I've got a UserForm that I've created with a textbox for date entry. When a user completes this form, this date populates on a sheet in the workbook. In the adjacent cell, I have a formula that adds 365 days to that date and is formatted to show if the date is occurring soon (60 days out, 30 days out). But, if there's no date in this referenced cell, then the formula adds a year to another date in the next adjacent cell. here's the formula for that date:

=IF([@[First Certification Date]],IF(ISNUMBER([@[Date of Last Annual]]),[@[Date of Last Annual]] 365,[@[First Certification Date]] 365),"")

Worth mentioning that the information in the user form is also updated on another sheet, with only this date being updated on the sheet in question.

Here's my VBA code:

Sub Submit()

Dim sh As Worksheet
Dim trow As Range
Dim annrow As Range
Dim ans As Worksheet
   
    
Set sh = ThisWorkbook.Sheets("NSE Tracker")


    
With sh

    .Range("F13").EntireRow.Insert
    .Range("F13").Value = UserForm.txtDate.Value
    .Range("G13").Value = UserForm.txtName.Value
    .Range("H13").Value = UserForm.txtInitials.Value
    .Range("I13").Value = UserForm.cmbFac.Value
    .Range("J13").Value = UserForm.cmbPos.Value
    .Range("K13").Value = UserForm.cmbType.Value
    .Range("L13").Value = UserForm.txtScore.Value
    .Range("M13").Value = UserForm.txtEvaluator.Value
    .Range("N13").Value = UserForm.txtRem.Value
    .Range("O13").Value = IIf(UserForm.optEvalNo.Value = True, "No", IIf(UserForm.OptEvalNA = True, "N/A", "Yes"))
    .Range("P13").Value = IIf(UserForm.OptNRNo.Value = True, "No", IIf(UserForm.OptNRNA = True, "N/A", "Yes"))
    .Rows.AutoFit

End With

If UserForm.cmbType = "Annual" Then

Set ans = ThisWorkbook.Sheets("Facility Annual Tracker")

With ans
    Set annrow = .Range("F:F").Find(What:=UserForm.txtName, LookAt:=xlPart)
                            
    Set trow = Range("I:I").Rows(annrow.Row)

    .Cells(annrow.Row, "I").Value = UserForm.txtDate

                    
    End With
    End If

End Sub

Only if the type of evaluation is "annual" will this section of code populate that second sheet with the date. The code finds the user and updates their line effectively, but the formula in the adjacent cell does not calculate based on it.

One solution I've tried is to change the format of the cell in question to a date format, thinking that maybe it's automatically formatted as "text" from the userform.txtdate entry:

    .Range("I:I").NumberFormat = "mmm-yy"

and what's strange is that it effectively changes the format for all the cells in that range (the other dates of the other users) but NOT the cell that I've just filled with the date.

I'm a beginner with VBA, sorry if my code looks bad!

Image of date entry on userform

CodePudding user response：

Please, use the next function:

Function TextToDate(strText As String, sep As String) As Date
   Dim arrD: arrD = Split(strText, sep)
   
   TextToDate = DateSerial(CLng(arrD(2)), CLng(arrD(0)), CLng(arrD(1)))
End Function

You can test it using:

Sub testTextToD()
   Dim x As String
   x = "8/17/2022"
   Debug.Print TextToDate(x, "/")
End Sub

     .Range("F13").Value = TextToDate(UserForm.txtDate.Value, "/")

If UserForm.txtDate is the text box where you need copying the string as date...