Using VBA to Prepare Cell Formulas En Masse With Absolute References

And How That Can Cut Down On Your Workload

Image for post
Image for post

Why You Should Know How To Prepare Cell Formulas Using VBA

It’s Easy To Enter Formulas In Cells!

Image for post
Image for post
Code to set Cell A1 to equal the sum of cells A2 and A3.
Image for post
Image for post
Cells A2 and A3 set to 5 and 6, respectively. Cell A1 was modified by the script to use the formula we specified.
Image for post
Image for post
Now we can see the two new rows of values that we have created to add together.
Image for post
Image for post
Here the code will apply the same formula to each cell between cell B1 and Z1.
Image for post
Image for post
Confirmation that the cells will still only add the values from A2 and A3
Image for post
Image for post
VBA Code for creating an absolute reference for other cells in your formulas.
Image for post
Image for post
The Results of Split(Cells(1,2).Address, “$”)
Image for post
Image for post
Row one now correctly adds row two and row three in each column.

Written by

Freelance Writer and Programmer. https://PatrickFluke.com

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store