Hi king ... first, can I suggest this to replace the structure of your existing set of sequential If statements? 
Function findCode(strIn As String) As String
Dim varTitles As Variant, varCodes As Variant
Dim i As Integer
    varTitles = Array("CAMP MIRAGE BASED UNITS/TACTICAL AIRLIFT UNIT", "CAMP MIRAGE BASED UNITS/THEATRE SUPPORT ELEMENT", "KABUL and OTHER LOCATION UNITS/MISC HQ and STAFFS")
    varCodes = Array("A – CM – TAU", "B – CM TSE", "C – MISC HQ & STAFF")
    Select Case strIn
        For i = LBound(varTitles) To UBound(varTitles)
            Case intstr(strIn, varTitles(i)) > 0
                findCode = varCodes(i)
                Exit Function
            Case Else
                'trap if not found a match:
        Next i
    End Select
End Function
You'll need to fill in the rest of your titles and codes in the arrays ... I actually suggest you do this as a public constant so it's not set everytime the function is called .. doesn't make much difference, but it's not technically efficient or needed.

As for adding the 2nd condition, there is nothing in there that identifies a current cell or references a cell in a loop, so you'll either need to pass it in or do it outside the function. If I understand what you're asking though, shouldn't you only call the function in the first place if there is a date in column N of the current row? If there isn't ... then don't call the function at all. Let me know if I've misunderstood this last bit though:-)