insert row in protected sheet by keeping formula

Anonymous
2011-08-13T08:15:07+00:00

Hi

I have a protected sheet contain formula in each row depends to above row( for example C2=C1+1; C3=C2+1;...).and cells contained these formula are locked.

I like to allow users to insert row everywhere by keeping formula without unprotecting the sheet. When I am clicking "insert row" while protecting the sheet, users could insert a row but the locked inserted cells are not including any formula and excel doesn't permit to copy formula of above cells. is there any solution?

Thanks

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
HansV 460.5K Reputation points MVP Volunteer Moderator
2011-08-14T10:16:32+00:00

If you just want the result of the formula, try

ActiveSheet.Range("E" & r).Value = ActiveSheet.Range("E1").Value

Was this answer helpful?

1 person found this answer helpful.
0 comments

42 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 460.5K Reputation points MVP Volunteer Moderator
    2014-08-05T18:21:07+00:00

    See my last reply on page #2 of this thread - I'd need to see a copy of your workbook.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2014-08-05T17:43:40+00:00

    thats what i get on your demo when i tested your theory out on inserting where a line has no formula's above it just to see the difference.

    What i get on mine is  application defined or object defined error and there is formulas in the cells above it.

    Was this answer helpful?

    0 comments
  3. HansV 460.5K Reputation points MVP Volunteer Moderator
    2014-08-05T17:36:27+00:00

    The error message "No cells were found" means that there were no cells with formulas in the row.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2014-08-05T17:21:00+00:00

    it does have formulas.

    row 20 is where my formulas start and I made the header row 19 (but I played with this some sam result every time)

    it says application defined or object defined error. with the error code beint 1004. I did test your demo to see what it would do if i inserted on the first line and i got the 1004 error but it said cell could not be located or somthing to that affect.

    Was this answer helpful?

    0 comments