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]

Found it! Check out hiker95's response here: http://www.mrexcel.com/forum/genera...681417-help-working-out-your-excel-level.html

 

[amou7]

and how do I add another one? do i just redo the whole code and place it under "End Sub"?

 

[amou7]

ok, I added it to after the end sub, but when i replaced the formula in the code to "=IF(ISERROR(VLOOKUP(LEFT(H46,4),test!A:B,2,0)),"N/A",(VLOOKUP(LEFT(H46,4),test!A:B,2,0)))", there is an error (Compile Error: Ambiguous name detected: Worksheet_Change) and it highlights the "N/A" ?[TABLE="width: 72"]
<tbody></tbody>[/TABLE]

 

[Joe4]

Follow the steps I outlined at the bottom of post #8:

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 <ACRONYM title="visual basic for applications">VBA</ACRONYM> format, and then add it to the code above.

By the way, which version of Access are you using?

 

[amou7]

It's Microsoft Visual Basic 6.5...
OK, I think i've got it... I just entered the new corrected line right after, 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 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)"
Cells(Target.Row, "P").FormulaR1C1 = "=IF(RC[-2]=""7ème Liberté"",""TO DO"",""N/A"")"
Application.EnableEvents = True
End If
End If

End Sub

thank you for the link, just saw it!

 

[Joe4]

It's Microsoft Visual Basic 6.5...

I wanted to know your Excel version, not your VBA version.

If you are using Excel 2007 or later, formulas like...

=IF(ISERROR(VLOOKUP(LEFT(H46,4),test!A:B,2,0)),"N/A",(VLOOKUP(LEFT(H46,4),test!A:B,2,0)))

Can be replaced with a simplified IFERROR formula (new to Excel 2007):

=IFERROR(VLOOKUP(LEFT(H46,4),test!A:B,2,0),"N/A")

 

[amou7]

sorry, yes, excel 2007 - but now I realized that i'm still confused! the formula i entered above should fall into column R, and I had it reading P - so if based on entering info in column H stays the same, how can i get a new formula into column R? thank you!!
the IFERROR was a copy, paste that I touched up (still a rookie to excel)

 

[Joe4]

You need to change the column reference in the formula!

Cells(Target.Row, "R").FormulaR1C1 = ...

 

[amou7]

Yes, that much I've got, but what about:

' Check to see if column P is blank
If Cells(Target.Row, "P") = "" Then
' Enter formula in column P

? it doesn't matter? or do i copy and paste the whole thing? and if so, where? I'm really sorry - hope i'm not being a headache!!
and lastly, IF i delete in column H, then i get this message:
Run time error '13'
Type mismatch... when i click on debug, then it highlights --> If Cells(Target.Row, "P") = "" Then

thank you for your patience!!

 

[Joe4]

If all the formulas in a column are the same, then it really doesn't matter. You can actually get rid of the whole part that checks to see if there is any value in column P before entering a formula in it (it doesn't hurt to overwrite a formula with the same formula).

When you say deleting in column H, are you just clearing the contents of that cell, or are you actually deleting the whole row?