Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2006
    Posts
    162

    Unanswered: Macro to change all formulas.

    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

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    what about just a find and replace
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Sep 2006
    Posts
    162
    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.
    Last edited by Jay59; 09-18-06 at 01:21.

  4. #4
    Join Date
    Sep 2006
    Posts
    162
    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?

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    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.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Jul 2005
    Posts
    39
    Would this help? insert 50 rows above the table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •