Changing Formula References in Visual Basic

  • Thread starter Thread starter EmmaC.
  • Start date Start date
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
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Changing Sheet Cell Formula Reference 3
How do I reference cells in CF formula within loop 2
Mid column sum formulas with varied ranges to sum 17
ActiveSheet in formula 2
Reference Separate Sheet for Formula 2
case sensitive IF, Visual Basic macro IF 2
Changing Formula References 1
Inputting a formula with a Macro 2

Back
Top