VBA Problem? Formula not updating cell value after VBA clears referenced cells.

[benjhardie]

Hello.

I have some code that does some stuff and then clears the user form. However, one cell in the user form contains a formula that returns a blank cell if another cell in the form is blank. The formula does what it should before you run the code but after the code is run and the rest of the cells are cleared, this cell's data remains. I already have 'Application.Calculation = xlCalculationAutomatic' at the end of the code so I really can't work out what the problem is!

This is how it appears after the code is run. But End Date (the cell containing the formula) should be blank as Start Date is now blank (cleared by the VBA)
Screen Shot 2018-08-03 at 09.45.16.png

Here is the formula

=IF(C9="","", IF(C8="Complimentary","N/A",IF(C8="Comp. (1 month)",EDATE(C9,1),IF(C8="Comp. (2 month)",EDATE(C9,2),IF(C8="Direct Debit","N/A","")))))

And my code;

Sub AssignLockerBtn()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Male.Unprotect Password:=""
Female.Unprotect Password:=""

'Check for missing fields
Dim r As Range
Dim totalCells As Integer
Set r = ActiveSheet.Range("C4:C10")
totalCells = r.Count - WorksheetFunction.CountBlank(r)
If totalCells = 7 Then

Dim N$, M$, S$()
N = Range("C4")
M = ""
S = Split(N)
If UBound(S) < 1 Then Call FullNameRequired
S(0) = Left(S(0), 1) & "."
N = Join(S)

If Range("C6") = "M" Then
Dim LockerM As Range
Set LockerM = Male.Range("C12:AO40").Find(AssignLocker.Range("C7"), LookIn:=xlValues, Lookat:=xlWhole)

    If LockerM Is Nothing Then
    MsgBox "Invalid Locker #"
    ElseIf LockerM.Offset(1, 0) <> "" Then
    MsgBox "This locker is already assigned to a member. Please choose a different locker"
    Else
    LockerM.Offset(1, 0) = N
    LockerM.Offset(2, 0) = AssignLocker.Range("C5")
    LockerM.Offset(3, 0) = AssignLocker.Range("C8")
    LockerM.Offset(4, 0) = Format(AssignLocker.Range("C9"), "DD/MM/YY") & " - " & Format(AssignLocker.Range("C10").Value, "DD/MM/YY")
    'Success message box
    MsgBox "Locker assigned successfully!"
    'Clear entry fields
    Range("C4: C9") = ""
    Range("C4").Select
    End If

ElseIf Range("C6") = "F" Then
Dim LockerF As Range
Set LockerF = Female.Range("C12:Q40").Find(AssignLocker.Range("C7"), LookIn:=xlValues, Lookat:=xlWhole)

    If LockerF Is Nothing Then
    MsgBox "Invalid Locker #"
    ElseIf LockerF.Offset(1, 0) <> "" Then
    MsgBox "This locker is already assigned to a member. Please choose a different locker"
    Else
    LockerF.Offset(1, 0) = N
    LockerF.Offset(2, 0) = AssignLocker.Range("C5")
    LockerF.Offset(3, 0) = AssignLocker.Range("C8")
    LockerF.Offset(4, 0) = Format(AssignLocker.Range("C9"), "DD/MM/YY") & " - " & Format(AssignLocker.Range("C10").Value, "DD/MM/YY")
    'Success message box
    MsgBox "Locker assigned successfully!"
    'Clear entry fields
    Range("C4: C9") = ""
    Range("C4").Select
    End If
End If

'Missing entry message box
Else: MsgBox "Please complete all fields"
End If

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Male.Protect Password:=""
Female.Protect Password:=""

End Sub 

I have also attached a copy of my workbook so you can see it in action.

I'd really appreciate the help! Is this a problem with my code or just a stupid formula error? Can't work it out!

Thank you!