Changing Formula References in Visual Basic

E

EmmaC.

I have matrix (221 by 221) that I need to convert into a
list with very particular spacing and formating for use in
another program.

I'm using a looping system to work between the different
cells in the sheet which will initially just contain the
formatted data. Anyway to use visual basic to insert
formulas in these cells where the referencing system is
tied to the number of the iteration in the looping
procedure? ie, inserting the formula = sheet1(B3) in the
new sheet, where the B3 part is generated automatically in
the looping process?

Cheers
 
B

Bernie Deitrick

Emma,

You don't really go into details of your looping or logic for coming up with
the formula, but let's say that you loop on rows and columns for both source
and reference. Then something like

Cells(Row1,Col1).Formula = "=Sheet1!" &
Cells(Row2,Col2).Address(False,False)

will result in

=Sheet1!B3

when the indices Row2 = 3 and Col2 = 2.

HTH,
Bernie
MS Excel MVP
 
Top