Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jul 2001
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert with Formula (Excel V97)

    <P ID="del"><FONT SIZE=-1>Post deleted by GaryC</FONT>

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

    Re: Insert with Formula (Excel V97)

    If you don't want your cell references relocated when you insert/delete cells/rows/columns, then you need to use indirect references like this:

    <pre>=INDIRECT("A7")
    </pre>


    That will always refer to the cell A7.
    Legare Coleman

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Insert with Formula (Excel V97)

    If you select the row you want to copy and run the following code, a new row will be inserted above and should contain only the formulas from the copied row. <pre>Sub InsertWithFormula()
    Selection.Copy
    Selection.Insert Shift:=xlDown
    Application.CutCopyMode = False
    On Error GoTo NoConstants
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Selection.ClearContents
    NoConstants:
    On Error GoTo 0
    End Sub</pre>


    Is that what you are looking for.

    Andrew C

  4. #4
    New Lounger
    Join Date
    Jul 2001
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert with Formula (Excel V97)

    Andrew C - Many thanks - Its very close to what I wan't - but it creates an 'error' in the fomula line below the one inserted - it refers to the line that was there not the new line.

    I will try to understand the code you wrote and adapt it - problem is I am an Access vb person and have not got my head round Excel yet !

    Cheers

  5. #5
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Quakertown, PA, Pennsylvania, USA
    Posts
    517
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert with Formula (Excel V97)

    Forget my post. Basically, Andrew has given you the same info, which I didn't realize until I wrote it. Ever do that? Doh!

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Insert with Formula (Excel V97)

    Not too sure what the error might be. Could you give some indication of the type of formula, and has absolute or relative addressing ?.

    What you are trying to do is insert a blank row with the same realtive formulas as the row beneath, right ?

    Andrew C

  7. #7
    New Lounger
    Join Date
    Jul 2001
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert with Formula (Excel V97)

    I think I have solved the problem with a bit of tweaking - I had a mixture of relative and absolute values - some fomulas were working across rows and some down columns - it was the 'down' fomulas that were going astray.

    Thanks <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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