CHANGING FORMULAS WITH VBA !

[jay dean]

In SheetB, a table with range (B5150) contains linked formulas,
example cell B5 contains the formala =Sheet7!C6 , cell B6 contains the
formala =Sheet7!C58 e.t.c

I want to create a second table of the same range (B155300) in SheetB
below the first one with each cell in the new table containing the exact
same formula as its corresponding cell in the first table with this
restriction: The cell refence portion of the formula of each cell in the
second table will be plus 1 row.

Example: range("B5").formula=Sheet7!C6 so,
range("B155).formula=Sheet7!C7 (i.e + 1 row)

range("B6").formula=Sheet7!C58 so,
range("B155).formula=Sheet7!C59 (i.e + 1 row)

Basically the macro will copy the SAME formulas to a new location
modifying only the row portion of the copied formula to one row more. I
would appreciate your help. Please I need a macro. Thanks.
Jay Dean

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Don Guillett]

something like this might do it for you.
Notice I copy one row down at d15

Sub newtable()
Range("e14:e17").Formula = Range("d15:d18").Formula
End Sub

 

[jay dean]

Don -
This does not work. The cells in the original range have formulas that
contain varying ranges. I just need to paste the same formulas with just
the range portion of the origainal formula increased by one row.

Your formula fills some cells and clears others that are supposed to
contain formulas. This should not occur because the new table is of the
same size. Remember the reference *in* the original formulas are not
sequential. They vary.I just need the same formulas copied to the new
range with the range part of the old formula increased by one row.
Something like: newcell.formula="='Same_SheetName_as_oldformula!' &
original_row_in_old_formula + 1 row.

Any help would be appreciated.

Thanks.
Jay Dean


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!