Problem with cell reference in my code. Need another pair of eyes!

[Bevg]

Hello everyone,

Attached is the result. Below is the section of my code the creates the result.



1) I need the date in column C to appear in column P for each instance of "FIRST AIRDATE" in column B.
a. date does not start in P4
b. dates do not format to dd/mm/yyyy for every cell. see orange
c. show zero if no date. see pink

My cell references look correct but I cannot figure out why the formatting is not working and as for the date "00/00/1900" I've tried several formulas and cannot get them to work to zero out that type of date. So here I'm looking for a suggestion as to what to try next.

'concatenate

    Range("M2").Select
    Do Until IsEmpty(ActiveCell.Offset(0, -11))
    ActiveCell.FormulaR1C1 = _
    "=IF(RC[-11]=""COMPANY :"",(RC[-10] & "" + "" & TEXT(R[2]C[-10],""dd/mm/yyyy"") & "": "" & R[3]C[-10]),R[-1]C)"
    ActiveCell.Offset(1, 0).Select
    Loop

Columns("M:M").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
'move company details to rows

    Range("N2").Select
    Do Until IsEmpty(ActiveCell.Offset(0, -13))
    ActiveCell.FormulaR1C1 = _
    "=IF(RC[-12]=""COMPANY :"",RC[-11],R[-1]C)"
    ActiveCell.Offset(1, 0).Select
    Loop
    Range("O2").Select
    Do Until IsEmpty(ActiveCell.Offset(0, -14))
    ActiveCell.FormulaR1C1 = _
    "=IF(RC[-13]=""SHOW TITLE :"",RC[-12],R[-1]C)"
    ActiveCell.Offset(1, 0).Select
    Loop
    Range("P2").Select
    Do Until IsEmpty(ActiveCell.Offset(0, -15))
    ActiveCell.FormulaR1C1 = _
    "=IF(RC[-14]=""FIRST AIRDATE :"",RC[-13],TEXT(R[-1]C,""dd/mm/yyy""))"
    ActiveCell.Offset(1, 0).Select
    Loop

Columns("N:P").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("Q2").Select
    Do Until IsEmpty(ActiveCell.Offset(0, -1))
    ActiveCell.FormulaR1C1 = _
    "=Concatenate(RC[-3],"" + "",RC[-2],"": "",RC[-1])"
    ActiveCell.Offset(1, 0).Select
    Loop
    
    'create headers for review
    Range("A1").Select
    Selection.EntireRow.Insert
    ActiveCell.FormulaR1C1 = "SourceFile"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "Company_upload"
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "COMPANY :"
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "SHOW TITLE :"
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "FIRST AIRDATE :"
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "ConcatenateFormula"
    Range("A1").Select
    
    Columns("M:Q").EntireColumn.AutoFit
        
ExitTheSub:
    ' Restore the application properties.
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = CalcMode
    End With
    ChDirNet SaveDriveDir