Filling blank cells in a column with a formula after inserting rows

[amou7]

Hello!

I am working on a flowtable of flight planning and obviously there is a lot of itineraries that are moved around, hence a lot of deleting rows and inserting rows. the deleting doesn't make an issue - it's the inserting that does! to the right of all the schedules, there are columns with formulas. When we insert rows, there are a lot of blanks where the formulas should be! As it's tedious (and not practical) to constantly copy and paste each time, is there an autofill formula? I've tried the ones where it fills in with the cell above, but after inserting a new row, the cell is empty.

I hope I've made sense? thank you for any advice!!

Excel 2007 on Windows 7
Anna

 

[amou7]

Just deleting the contents of the cell ...

 

[Joe4]

It shouldn't cause an error. Post your current version of the code.
Also note: I will only have limited internet access until Monday.

 

[amou7]

I don't know what happened, but it's fine now! no more errors!! thank you infinitely, Joe!

 

[amou7]

Hello, I am back with one more question! after lots of tweaking, my sheet is looking pretty good and your code really helped! Now, the updated code looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)

' Trigger when a single cell in column H us updated
If (Target.Count = 1) And (Target.Column = Range("H1").Column) Then
' Check to see if column R is blank
If Cells(Target.Row, "R") = "" Then
' Enter formula in column R
Application.EnableEvents = False
Cells(Target.Row, "R").FormulaR1C1 = "=VLOOKUP(LEFT(RC[-10],2),'col AB'!C[-17]:C[-16],2,0)"
Cells(Target.Row, "S").FormulaR1C1 = "=IF(RC[-2]=""7ème Liberté"",""TO DO"",""N/A"")"
Cells(Target.Row, "Z").FormulaR1C1 = "=IF((LEFT(RC[-18],2))=""ub"",""TO DO"",""N/A"")"
Cells(Target.Row, "AB").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(LEFT(RC[-20],4),test!C[-27]:C[-26],2,0)),""N/A"",(VLOOKUP(LEFT(RC[-20],4),test!C[-27]:C[-26],2,0)))"
Cells(Target.Row, "AC").FormulaR1C1 = "=IF((LEFT(RC[-21],2))=""oa"",""SQUAWK"",""N/A"")"
Cells(Target.Row, "AE").FormulaR1C1 = "=IF(RC[-1]=""RECEIVED"",""TO DO"",""N/A"")"
Cells(Target.Row, "AI").FormulaR1C1 = "=IF((LEFT(RC[-23],2))=""DG"",""PENDING"",""N/A"")"
Cells(Target.Row, "AL").FormulaR1C1 = "=RC[-1]/RC[2]"
Cells(Target.Row, "AO").FormulaR1C1 = "=RC[-2]*RC[-3]"
Cells(Target.Row, "AM").FormulaR1C1 = "=R[-1]C"
Cells(Target.Row, "AN").FormulaR1C1 = "=R[-1]C"
Cells(Target.Row, "X").FormulaR1C1 = "=IF((LEFT(RC[-16],2))=""oa"",""MRF"",""N/A"")"
Application.EnableEvents = True
End If
End If

End Sub

I would like to add a code to this one, where I repeat line 2 (titles) in every line where the word 'Mission' appears under column G... is it possible to add a separate code to this one?
I recorded a macro where it does this exactly as I would like, but I'd rather have it in a worksheet change code... how do i transform it?

Sub MISSION()'
' MISSION Macro
'


'
ActiveCell.FormulaR1C1 = "MISSION"
Range("H215").Select
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
Range("N215:AQ215").Select
Selection.FormatConditions.Delete
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
Range("N8").Select
Selection.Copy
Range("N215").Select
ActiveSheet.Paste
Range("O8").Select
Application.CutCopyMode = False
Selection.Copy
Range("O215").Select
ActiveSheet.Paste
Range("P8:AQ8").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
Range("P215").Select
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
Range("T218").Select
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 35
Range("A215:AQ215").Select
Range("AQ215").Activate
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 39
Range("N215:AQ215").Select
Range("AQ215").Activate
Application.CutCopyMode = False
With Selection.Font
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.TintAndShade = 0
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.349986266670736
.PatternTintAndShade = 0
End With
Range("N217").Select
End Sub

thank you so much!!