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

 

[Joe4]

Welcome to the Board!

One way to approach it is to use an Event Procedure, which is VBA code that automatically runs upon some event happening. One such event is called the "Worksheet_Change" event, which is triggered whenever a cell on the worksheet is updated (ca be limited to certain cells, rows, columns, etc). So, if you are manually inserting and typing values in, you could have it check at that point to see if there are any formulas in certain columns, and if there are not, enter them in.

 

[amou7]

Thank you, VBA sounds good, but what exactly is the code that I'd be entering? I've never done VBA... I saw where I can play around with it, but I'm a rookie to macros etc... Could you help me? It's column P and I'd like to have a formula in all the cells of the column after a row insert... Thx

 

[Joe4]

What is the formula that will be put in column P?
What is a good column that you would manually entering data into to that we could use for our "triggering" event?

 

[amou7]

the formula in column P: =VLOOKUP(LEFT(H16,2),'col AB'!A:B,2,0)
and columns A-M will have info manually entered (copy and paste suffices?)

Thank you!

 

[amou7]

could i send you a copy of the excel sheet?

 

[amou7]

I wanted to edit the post, but too late - because I am still trying to figure it out somewhat on my own - columns O to AM could all use this VBA - maybe there is a bulk way of doing it - and I was thinking if the formulas were all in a hidden row above (for example row 7) as a source? would that help? Thank you for all your help!

 

[Joe4]

Sorry, I have been tied up in meetings most of the morning.

OK, here is some VBA code that will do my idea. Anytime a single entry is made in column H, it will check to see if the formula already exists in column P. If it does not, it will populate the formula automatically.

You want to place this code in the Worksheet module of the sheet you want it to run on. An easy way to ensure this is to go to that sheet, right click on the sheet tab name at the bottom of the screen, and select "View Code". Now paste this code in the resulting VB Edtior window:

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 P is blank
        If Cells(Target.Row, "P") = "" Then
'   Enter formula in column P
            Application.EnableEvents = False
            Cells(Target.Row, "P").FormulaR1C1 = "=VLOOKUP(LEFT(RC[-8],2),'col AB'!C[-15]:C[-14],2,0)"
            Application.EnableEvents = True
        End If
    End If
        
End Sub

Just so you know how I got the VLOOKUP formula in that format, if you turn on the Macro Recorder and record yourself entering that formula in column P, and stop the recorder, that is the formula you get. The "R1C1" format does everything relative to the cell that the formula is being entered in, so it will work for any row.

If you have other formulas you need to enter, just use that method to get the formula in that R1C1 VBA format, and then add it to the code above.

 

[amou7]

Works like a charm!! thank you so much!! (can't say that all of it make sense to me - still a bit a foreign language) but would love to learn more! any place for VBA for dummies? thank you!
One more thing - the undo no longer works after this? there is an autosave that happens? Not that I can't get around it, but just would like to know the reason for it...

 

[Joe4]

Unfortunately, "Undo" does not work on VBA code.

There are various books out their on VBA, but I haven't used any in over 10 years, so I don't really have any good recommendations for you. However, there is a user here who has a whole list of recommendations. I'll have to see if I can find it.