Incorporate an Excel Formula in to this Macro

Hello,

I am currently working on a project that will track personnel while they are waiting for inter-department transfers. Once they have been waiting for 90 days we have to notify our manager for further instructions.

I have a button located in the upper left hand corner that when pressed activates the macro (launches a dialogue box for user data entry). I want to use two separate Excel Formula in the macro but I can't seem to get them to work. I want the formulas in the macro not in the cell because I know that in time someone will select an entire row and press delete, thus taking away the cell formatting/formula. I have been able to make some progress but cant find the correct syntax for the formulas.

I have looked through the forum library for guidance (i.e. examples) before asking my question but wasn't able to quite find what I was looking for. Any help with this would be great. Thanks.

Here is a copy of my code:

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdClearForm_Click()
Call UserForm_Initialize
End Sub

Private Sub cmdOK_Click()
ActiveWorkbook.Sheets("PERMANENT").Activate
Range("A3").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = txtName.Value
ActiveCell.Offset(0, 1) = cboABCD.Value
ActiveCell.Offset(0, 2) = txtDateArrived.Value
ActiveCell.Offset(0, 3) = cboABCType.Value
ActiveCell.Offset(0, 4) = txtDateIssued.Value
ActiveCell.Offset(0, 5) = txtBoardDate.Value
ActiveCell.Offset(0, 6) = txtQualifiedDate.Value
ActiveCell.Offset(0, 7) = cboQualifiedNotQualified.Value
If chkAAARequired = True Then
ActiveCell.Offset(0, 8).Value = "Yes"
Else
ActiveCell.Offset(0, 8).Value = "No"
ActiveCell.Offset(0, 8).Value = "No"
End If
If optYELLOW = True Then
ActiveCell.Offset(0, 9).Value = "Yellow"
ElseIf optBLUE = True Then
ActiveCell.Offset(0, 9).Value = "Blue"
ElseIf optORANGE = True Then
ActiveCell.Offset(0, 9).Value = "Orange"
End If
Range("A3").Select
End Sub

Private Sub UserForm_Initialize()
txtName.Value = ""
With cboABCD
.AddItem "A"
.AddItem "B"
.AddItem "C"
End With
cboABCD.Value = ""
With cboABCType
.AddItem "FRANK-O"
.AddItem "FOOD"
.AddItem "HELLO"
End With
cboABCType.Value = ""
txtDateArrived.Value = ""
txtDateIssued.Value = ""
txtBoardDate.Value = ""
txtQualifiedDate.Value = ""
With cboQualifiedNotQualified
.AddItem "Quald"
.AddItem "Not Quald"
End With
cboQualifiedNotQualified.Value = ""
chkAAARequired = False
optIntroduction = True
txtName.SetFocus
End Sub
Private Sub chkAAARequired_Change()
    If chkAAARequired = True Then
    End If
End Sub

The formulas that I am trying to use are as follows:

Task 1: In Column(K3) titled "Release Date" I need to add the value of +90 days based on the date value entered in Column(G3) titled "Qualified Date". I tried in the cell K3 =G3+90 and I get the desired results but I would like it to be part of the macro itself.

Task 2: In Column(L3) titled "Hold/Release" I want to use the IF Function.
I tried the following formula:
=IF(K3<=TODAY(),"Release",IF(K3>=TODAY(),"Hold"))
It works in the cell but I would like to have it work as part of the macro.

Thank You for your time and excellence.

Please see attachment.

Hello BusDriver2,

I made a few changes to your "OK" command button code. It will now transfer the data from the User Form to the next empty line on your database worksheet. This macro has already been changed in the attached workbook.

Private Sub cmdOK_Click()

  Dim Cell As Range
  Dim Rng As Range
  Dim RngEnd As Range
  Dim Wks As Worksheet
  
    Set Wks = ThisWorkbook.Worksheets("PERMANENT")
    Set Rng = Wks.Range("A3")
    Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
    Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, RngEnd.Offset(1, 0))

      Set Rng = Rng.Resize(1, 10)
        With Rng
          .Cells(1, 1) = txtName.Value
          .Cells(1, 2) = cboABCD.Value
          .Cells(1, 3) = txtDateArrived.Value
          .Cells(1, 4) = cboABCType.Value
          .Cells(1, 5) = txtDateIssued.Value
          .Cells(1, 6) = txtBoardDate.Value
          .Cells(1, 7) = txtQualifiedDate.Value
          .Cells(1, 8) = cboQualifiedNotQualified.Value
          If chkAAARequired = True Then
             .Cells(1, 9) = "Yes"
          Else
             .Cells(1, 9) = "No"
          End If
          If optYELLOW = True Then
            .Cells(1, 10) = "Yellow"
          ElseIf optBLUE = True Then
            .Cells(1, 10) = "Blue"
          ElseIf optORANGE = True Then
            .Cells(1, 10) = "Orange"
          End If
          .Cells(1, 1).Select
        End With
    
End Sub

Hello BusDriver2,

I had a good Easter. How about you?

If it is okay with you, I'll work on the formula additions after I get some sleep. Oyasumi nasai.

Hello BusDriver2,

Here is the code for the updated macro. This now adds the formulas to the cells when the data is transferred to the worksheet.

Private Sub cmdOK_Click()

  Dim Cell As Range
  Dim Rng As Range
  Dim RngEnd As Range
  Dim Wks As Worksheet
  
    Set Wks = ThisWorkbook.Worksheets("PERMANENT")
    Set Rng = Wks.Range("A3")
    Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
    Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, RngEnd.Offset(1, 0))

     'Range is columns A through L (1 to 12)
      Set Rng = Rng.Resize(1, 12)
        With Rng
          .Cells(1, 1) = txtName.Value
          .Cells(1, 2) = cboABCD.Value
          .Cells(1, 3) = txtDateArrived.Value
          .Cells(1, 4) = cboABCType.Value
          .Cells(1, 5) = txtDateIssued.Value
          .Cells(1, 6) = txtBoardDate.Value
          .Cells(1, 7) = txtQualifiedDate.Value
          .Cells(1, 8) = cboQualifiedNotQualified.Value
          If chkAAARequired = True Then
             .Cells(1, 9) = "Yes"
          Else
             .Cells(1, 9) = "No"
          End If
          If optYELLOW = True Then
            .Cells(1, 10) = "Yellow"
          ElseIf optBLUE = True Then
            .Cells(1, 10) = "Blue"
          ElseIf optORANGE = True Then
            .Cells(1, 10) = "Orange"
          End If
         'Add the formulas
          .Cells(1, 11).Formula = "=G" & Rng.Row & "+90"
          .Cells(1, 12).Formula = "=IF(K" & Rng.Row & "<=TODAY()," & Chr$(34) _
                                & "Release" & Chr$(34) & "," & Chr$(34) _
                                & "Hold" & Chr$(34) & ")"
         'Select the first cell in the new row
          .Cells(1, 1).Select
        End With
    
End Sub