Creating a macros that will insert a new row AND add defined formulas to cells

This is a response to one of my previous videos where we explain how to create a macros to insert a new row and also add formulas into the newly created row simultaneously. If you have not seen my previous video on this then please click the link below.

The code we have used in this example is as follows.

Sheets("Sheet1").Range("e4").Select
ActiveCell.Formula = "=sum(a4:d4)"

A great Beginner Macro book!

Remember if you have to add multiple formulas to multiple cells, just repeat the process with different cell selections and the defined formulas. And also remember that this code needs to be placed after the former code.

[Twoperscent]

Please take a second to help me out and SUBSCRIBE to the channel. YouTube rules are changing and your help is much appreciated. Thank You

[bernsbuenaobra]

Simple indeed but brilliant thanks. In very large data set say spectroscopic data where easily there are like 800x2150 cell elements in a single file for a sample for example. Say you want to take every four reflectances it's average this task, this would mean creating a macro that will insert blank rows every four rows and on each column a calculation of an average of the four rows. It will not be very practical to manually do this. You need to write the macro so that a module that will insert blanks can be called in a loop, then the calculation of averages has to be pointed correctly to the cell and paste the formula there. This could be a good extension in this presentation - provide a way to programmatically locate the cell and pasting the formula there.

[svepel]

How to inset new raw not up, but down?

[TrevorDans]

Absolutely superb mate. Best tutorial online by far. Thanks :)

[WTwistedM]

Hey man, what a fantastic find this was and thank you for the excellent tutorial.

I am on Mac using Excel 2016 (knowing that there might be some differences with the code as VBA changes for Mac, where they in 2013 took away VBA and made it using Real Basic or something like that)

For me to make the code to make an entry on the second Column of the Named Table, I had to make it look like follow. If anyone needs your code working on Mac.

please if you can see anything to explain why it worked for me, you are very welcome as I am still learning some more in depth coding...



Dim the_sheet As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow

Set the_sheet = Sheets("DataTables")
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row =

table_object_row.Range(1, 1).Value = Range("NewCountry")
table_object_row.Range(1, 2).Value = Range("NewFormat")



Note the second line for declaring the Value for the Cell named "NewCountry"

In this Workbook you can add the counties with currencies you would like to convert to.

Thanks

[deepakbhanushali]

i want to insert from where my cursor is then what to do

[komalmhatre]

Thank You. your formula helped me very much. I also merged some other functions with this formula and it worked well.

[renzformoso]

Hi I have a question, for example I am using the button macro for a certain row, for the same sheet I want to insert a new table with the button function. When I copy and insert, the command button still inserts on the previous rows, is there a way so that the new button will insert at the new table I inserted?

[iamjohnearvin]

do we have a formula or vba code for my situation ?Sheet1 contains 2 columns which is A and B. Column A is for NAMES and COLUMN B is the number of copies you want to generate. example Column A contains John in A1, and Column B with a number 10. IN SHEET 2 it will automatically create 10 John .

[phoebeolley]

Is there a way to use the same formula but instead of using a cell you can use a word? for Example, if the data varies each week how can you format for the data to be picked up in between two words?

[keningilbert]

The link to the previous video you mentioned and included in the description states the video no longer exists. Do you have a different link to reference please?

[pattytucker]

How to Insert a new row at the BOTTOM of the existing row. Previous video shows new rows by dropping down current row.

[riacharda]

Great tutorial. Is there a way to make the formula dynamic for rows?

So say for example, you want to add the new row *below* the existing row. How can the code be modified to edit the formula for a5 and onwards?

[bishnumahapatra]

Is there any formula - using "if" if logical test is true, insert a new row with certain data, if logical test is false, no row.

[chrismh]

Thanks did it, however if you subsequently protect the sheet and start adding rows it doesn't add the formulas - how can I protect the sheet and insert rows that adopts the formulas?

[robinstough]

This tutorial is very helpful. Is there a way I can copy a row that has the formulas and insert the whole thing at the bottom?

[thesimm]

Is there a formula that keeps the formatting/merging of the rows above? I have my add row button but can't figure out how to keep the formatting the same as the rows above on the new row that is created when pressing the command button

[JustinMcNabbIreland]

This is sweet but one thing, when i insert the new row, it copies the colours of the heading above. How do i make it copy the grids and colours below instead

[antonyyall]

Really well presented and easy to follow. Thanks very much... Your efforts are appreciated! One problem I am having... using
ActiveCell.Formula = "=IF(A166="Y", "N/A", "")"
but am getting
"Compile Error:
Syntax error"
I have checked the formula and it works outside of the macro no problem. Any help would be greatly appreciated. Thanks.

[lewisosborne]

@twoperscent Hi the formulae works great apart from the fact that i get an application defined or object defined error. Would this be because my formulae is a vlookup? I can't see why it would make a difference to be honest as it works fine in the cells I copy paste it into. Any help would be appreciated