VBA Index / match type formula copying formulas, not values

  • Thread starter Thread starter Kuba
  • Start date Start date
K

Kuba

Simplyfying my problem an awful lot, I am trying to write a macro that
would take a certain table, look up the vertical code and horizontal
code of certain items, copy a link from those items, change it to
absolute refernce (which part I know how to do) then go to another
table, and paste the link to the appropriate cell (with the same
vertical and horizontal code-cooridinates), then change it back to
relative reference (which again I know how to do).

I know how to do the first part, but this is also only using INDEX /
MATCH, so returning a value, not an actual link.

I have been browsing my VBA books and the groups for quite a long time
trying to find a solution - to no avail.

I would greatly appreciate any help

Kuba
 
It is unclear what the problem is. If you want to reference a value in Cell
B9, you put in a formula

set rng = Worksheets("Sheet1").Range("B9")
worksheets("Sheet2").Range("F32").Formula = "=" & rng.Address(0,0,,True)

I suspect you have two tables with the same row and column labels, but
perhaps in different order on each table - and you want to fill out table 2
with links back to the appropriate row and column intersection on table 1.
Is that what you want?
 
Yes, I have two tables with almost the same row and column labels, which may be in different order. It is an input sheet for a financial model, which is connected to numerous sources. What I am after is not to link the new sheet to the old sheet, but to copy the formulas (i.e. links) of the old sheet into the respective places of the new sheet
 
Back
Top