As discussed in my previous blog post, this week I will go through how to input a formula within a macro. Here is the generic formula which needs to be inputted in the module tab of the sheet (i.e. press ALT+F11 > Insert>Module)
Range("CELL").Formula = "=FORMULA”
In the formula above input the reference cell where there is a the word CELL and input the formula where there is the word FORMULA.
Reasons for having a formula within a macro:
Here’s an example: let’s input total revenue in Cell F3 of our spreadsheet
Now let’s use a more advanced formula. i.e. COUNTIF statement with multiple criteria. The important note about this is to make sure not to enter any values within the formula itself. Rather we must enter references to those values (from within the spreadsheet).
=SUMPRODUCT(COUNTIF(B2:B30,'Sales Branch List'!$A$2:$A$4))
For example, the formula above returns total number of IDs belonging to sales branches located in either “BARRIE","KINGSTON", or "BRAMPTON". However, I have not inputted these values directly into the formula but rather inputted the reference to them located within a different tab of the spreadsheet (i.e. Cells A2, A3, and A4 of the “Sales Branch List” tab). For more information on this please visit my previous blog post.
And here is the outcome of both formulas executed directly through a macro.