Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jan 2002
    Location
    Victoria, Br. Columbia, Canada
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How Can I Edit Many Formulae Simultaneously? (2000 SR-1)

    I have a worksheet with hundreds of cells containing (variable length) formulae, such as "=G$7*$G8". I need to round these values to 0 decimal digits so I need to change the formulae to look like "=ROUND(G$7*$G8),0)". There are too many cells to edit them individually.

    Can anyone suggest an approach that would enable me to change them all at once? If I could use regular expressions in Edit/Replace I'd be able to do it, but I don't believe they can be used. Any and all ideas would be greatly appreciated!

  2. #2
    New Lounger
    Join Date
    Jan 2002
    Location
    china
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How Can I Edit Many Formulae Simultaneously? (2000 SR-1)

    I think you can use "find" function, you find all "=", than replace it with "=round(( ),0),
    next you can insert the formula. please try it.
    another, you also can make a excel macro to do it.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How Can I Edit Many Formulae Simultaneously? (2000 SR-1)

    See if <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=86124&page=&v iew=&sb=&o=&vc=1#Post86124>This Thread</A> will help.
    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Jan 2002
    Location
    Victoria, Br. Columbia, Canada
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How Can I Edit Many Formulae Simultaneously? (2000 SR-1)

    This was perfect! My attempts to search for related postings like this went down the wrong track.
    Many thanks!

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: How Can I Edit Many Formulae Simultaneously? (2000 SR-1)

    Linda

    Happy New year. You are on the right track, but if you replace all =s what will happen to something like:
    =IF(A1=0,B7=D7,B7=E7)

    What I would add to your suggestion is to use CONCATENATE and concatenate =ROUND( to the formula already in the cell and then add the concatenation of ,0).

    And I would prefer to use VBA to do all that.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  6. #6
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How Can I Edit Many Formulae Simultaneously? (2000 SR-1)

    Something that you can do so that a rang of cells that will have a same one formulates...

    1. Beginning in the first cell up-left, highlight all the range, for example (B210), active cell (B2)

    2. Write your formula, but without make [Enter], you must make [Ctl]+[Enter]

    P. S. Takes care of, to indicate very well the relative and absolute cells

Posting Permissions

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