insert row in protected sheet by keeping formula

Anonymous

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

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 MVP Volunteer Moderator

If you just want the result of the formula, try

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

0 comments

42 additional answers

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more
Anonymous

2011-08-14T06:27:16+00:00

Thanks so much.This solution covers all my requirement. I'll test it and come back to you if faced with any problem.

Best regards
Was this answer helpful?

0 comments No comments
Anonymous

2011-08-14T09:13:13+00:00

Dear Hans

It's working. the only problem is:

I am trying to put formula for some cells in inserted row for example E(r)=E1.

while I have a constant figure(i.e 400) in E1 it's working, but when I have a formula in E1 with result of 400, it doesn't work. E(r) is 0 or empty in this case!
Was this answer helpful?

0 comments No comments
HansV 460.5K Reputation points MVP Volunteer Moderator

2011-08-14T09:38:32+00:00

What code are you using now?
Was this answer helpful?

0 comments No comments