VBA to add a new row to a specified table and insert a formula

[RSAmon]

Hello

This is my first post so I'm hoping someone can help me (and that I've stuck to the rules!).

I'm trying to set up a small database within Excel for work. In this, I have a table called OFI_Data which will hold all the records. Within the OFI_Data table, there is a column called Target Date which will initially calculate the Target Date as:

=IF([Raised Date]="","TBC",[Raised Date]+30)

However, if an extension is required, the Target Date will be manually adjusted later on so I can't just rely on the autofill function of the table.

I also have a Create New Record button which has been assigned the following (very basic) Macro:

Sub Create_New_Record()
ActiveSheet.ListObjects("OFI_Data").ListRows.Add AlwaysInsert:=True
End Sub

This code works perfectly to add a new row to the bottom of the table, providing I am already in the table. If a cell outside the table is active though, the button doesn't work.

My question is, please could someone help me with the VBA code to:

• Insert a new row at the bottom of the OFI_Data table regardless of what cell is active on the worksheet (which is called Database)
• Insert the default 30 day Target Date formula into the Target Date column when the new row is added (even though that formula might not be used in the cell directly above it)

I hope this is clear enough for someone to offer some help. I have tried looking online but most answers either involve using the autofill function, or copying the formula from the row above which I can't use in this case.

If you need any more information, please let me know. Thank you in advance!
R

 

[ranman256]

alter the names to fit your sheet.
and the relative position for the formula....
here: RC[-2] says the Raised Date ​cell is 2 columns to the left.

Sub Create_New_Record()
sheets("database").activate
range("A2").select
ActiveSheet.ListObjects("OFI_Data").ListRows.Add AlwaysInsert:=True
r = activecell.row
range("A" & r).FormulaR1C1 = "=IF(RC[-2]="""",""TBC"",RC[-2]+30)"
End Sub

 

[RSAmon]

Hello - sorry for the delay, finally managed to get time to try this out in the database and it worked beautifully so thank you very much. The only thing I did notice was it also put this formula into the A2 cell on the worksheet, which isn't part of the table. I'm not sure how to stop that from happening, but it's not a big deal (I've just made the text colour white on that cell so no-one will ever know!) Thank you again!