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

Chrican

New Member
Joined
Jul 18, 2011
Messages
42
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!
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Watch MrExcel Video

Forum statistics

Threads
1,118,862
Messages
5,574,719
Members
412,615
Latest member
John_W_Excel
Top