How to add formula in Macro – Microsoft Excel

posted in: Excel Tutorials | 6


Microsoft Excel Tutorial – Excel Macro Formula

excel smalll

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

Sheets(“Sheet1”).Range(“e4”).Select
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.

 INR and add formula workbook

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.

6 Responses

  1. Melissa Weiland

    I have used your code to create a macro button to add rows to a spreadsheet and it works like a charm, thank you! I do have one question though. Some of my cells in each row are merged and when I use the button, it adds another row, but does not copy any of the merging. How do I make it do that?

    • twoperscent

      Hi Melissa

      Let me look into that for you. I will keep you posted.

      Twoperscent

    • twoperscent

      Melissa, my apologies for the delay, please find attached link and video explaining how to merge cells once a new row has been inserted.

  2. Hi 🙂

    Thank you for this, really helpful! However, I’m encountering an error when trying to use the formula
    ActiveCell.Formula = “=IF(J8<=F8,”Profit”,”Loss”)”

    The error showing up is Compile error: Expected: end of statement

    Hope you can help 🙁

  3. I totally agree with you.

  4. Rodrick

    Your work isgreat, so easy 5o learn from thank you! Is there a Ms word site you have or one you would recommend?

    Thx Rodrick

Leave a Reply