Microsoft Excel Tutorial – Excel Macro Formula
How to add formula in Macro – Microsoft Excel
For a guide to how to edit macros for your own purposes, please click here.
In this video we outline how to create a Macro in Microsoft Excel which adds a new row to a defined location and also inputs defined formulas into the newly created cells. This video is a next step to a former video where we outlined how to add a new row only, which can be located here.
This is very useful if you would like to keep information in chronological order and quickly create new rows in you spreadsheet. This is perfect if the new cells you are creating also need formulas to be added, as this macro will perform the entire function once executed.
In this Excel Tutorial we use VBA code which is explained in more detail below, we will call this “VBA Insert formula”.
The video can be located below.
If you have any questions please post these onto the YouTube channel.
Excel Macro Formula video
The code that is used in this video is shown below. This code will not insert a new row, it will simply add your Excel formula in VBA to the cell you have defined. Our Excel VBA cell formula in this example is =sum(a4:d4) and therefore if you are using a different formula you will have to edit between the parenthesis so the VBA will insert formula you have defined. Adding formula in Macro is quick and efficient when done in this manner especially when combined with other macro VBA, such as the inserting of new rows.
VBA Insert Formula
ActiveCell.Formula = “=sum(a4:d4)”
You will need to change the range for your own purposes (click here for further details), and you would also need to edit the formula that has been used for example purposes in the description. For example, if your desired formula is =IF(J8<=F8,”Profit”,”Loss”), your line of code would read.
ActiveCell.Formula = “=IF(J8<=F8,”Profit”,”Loss”)”
To download a copy of the workbook that is used in this tutorial, please click the link below.
This concludes how to add formula in macro using the above VBA insert formula code. This can be added into the macros you create.
For more Excel Tutorials please visit my channel at the link here.
If this has been helpful then please like or subscribe to the channel.