Macro to change all formulas.

Jay59 · #1 (**permalink**) 09-17-06, 22:26

I have a table that is 50x50 so it has 2500 fields. Each field has a formula.

ex. =TRANSPOSE(Data!B11:B15)

Lets say i want to change ever number by addine 50 to it

so it looks like this

=TRANSPOSE(Data!B61:B65)

Any ideas on how I could do this?

Thanks

myle · #2 (**permalink**) 09-18-06, 00:13

what about just a find and replace

Jay59 · #3 (**permalink**) 09-18-06, 00:17

the formulas are all differant

=TRANSPOSE(Data!B11:B15)
=TRANSPOSE(Data!B16:B20)
=TRANSPOSE(Data!B21:B25)
=TRANSPOSE(Data!B26:B30)

Thast just 4 of them.

It took me about an hour to do one page. I have 24 more to do

I wanted to automate it as much as possible.

Jay59 · #4 (**permalink**) 09-18-06, 15:46

Can anyone else think of a better way to copy the page and edit the formula, without doing each one manually?

Can you edit an array formula one array at a time?

shades · #5 (**permalink**) 09-18-06, 19:58

I assume you are wanting to do this with VBA.

You define the Row number, then add a constant to that. If you want the person to decide what that constant is, you can Dim it as Long, then include an InputBox line so that the user can determine what to add to each.

taurus · #6 (**permalink**) 09-18-06, 23:03

Would this help? insert 50 rows above the table.

Internet Services

Web Development

Digital Marketing

Consumer Tech