VBA to populate table of formula's relative to active cell

Chrican

New Member
Joined
Jul 18, 2011
Messages
44
I’d like to build a macro that sets up a bunch of linked formula’s in a range relative to the active cell, which is getting a bit advanced for me. I’ve generalized it a bit for anonymity, but what I’d like is for the macro to do the following steps:


  1. Relative to the active cell, check that the range 3 columns x 11 rows is empty. If not, prompt “Overwrite cells?” Yes/no.
  2. In column 2, row 1, insert “Lookup”
  3. In column 2, rows 2 through 11, insert “Revenue”
  4. In column 3, row 1, insert “2019” (i.e. the year)
  5. In column 3, rows 2 through 11, insert formula A (see below)
  6. In column 1, rows 2 through 11, insert formula B from below
  7. Bonus: Typically, each of these formula’s is a bit laggy when you enter it in (linked network files) – is there a way to pause all that, insert the text, then let them calculate at the last step, all at once? Maybe disable update links? Turning off automatic calculations doesn’t seem to help when I do it the non-VBA way.

Basically what this does is builds a simple table that index/matches data from each of 10 company models that I maintain, which we then use to compare metrics across our models. It’s meant to make the process a lot quicker than having to open each file and link them manually, or find+replace each file name, etc. Once the table is populated I can mess with the years and lookups or add more columns as needed.

Formula A – Index/matches the lookup from above, in column A of each model, and the year from above in row 4 of each model (these are based on running the macro from A1; anchoring the references to the second column and first row is important) (each of the ten times this is entered would be a different, but specific, file obviously):
Code:
=INDEX('X:\Filepath[ABC.xlsm]Model'!$1:$1048576,MATCH($B2,'X:\Filepath\[ABC.xlsm]Model'!$A:$A,0),MATCH(C$1,'X:\Filepath\[ABC.xlsm]Model'!$4:$4,0))

Formula B – Identifies which model each formula is linking to for auditing purposes:
Code:
=MID(FORMULATEXT(C2),FIND("[",FORMULATEXT(C2),1)+1,FIND(".xls",FORMULATEXT(C2),1)-FIND("[",FORMULATEXT(C2),1)-1)

Any help would be appreciated!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top