Hi,
I have an excel sheet with lots of formulae in it. The value in column C
will change when the value in Column A changes (Col C has the formula). I
dont want the user to edit Col C and so i have locked C and protected the
sheet. But the user can edit Col A. When Col A is changed i am unprotecting
the sheet, calculating Col C formula(Might also insert new rows based on Col
A) and then again protecting it ( all through code) .But sometimes this code
gives me #Value error in Col C when Col A changes. Any solution for this?

Thanks.

It's almost impossible to guess what solution would be appropriate when
you haven't told us what functions you have in column C...


In article ,
swtupr wrote:

Hi,
I have an excel sheet with lots of formulae in it. The value in column C
will change when the value in Column A changes (Col C has the formula). I
dont want the user to edit Col C and so i have locked C and protected the
sheet. But the user can edit Col A. When Col A is changed i am unprotecting
the sheet, calculating Col C formula(Might also insert new rows based on Col
A) and then again protecting it ( all through code) .But sometimes this code
gives me #Value error in Col C when Col A changes. Any solution for this?

Thanks.

There are several solutions, it would be best to know the kind of
formula that is in COLUMN C. But a generic answer is to first test your
formula with an IF(ISERROR() test, and suppress the error message. But
those error messages are useful, so suppressing them can also suppress
an issue. Up to you to know if that is the case.

Anyway, let's take a simple formula and wrap it in an IF(ISERROR()
trap.

If your formula was something like =VLOOKUP(A2,G2:H200,2,0) and that
was causing a #VALUE error, you can trap it like so:

=IF(ISERROR(VLOOKUP(A2,G2:H200,2,0)),0,VLOOKUP(A2, G2:H200,2,0))

This works, but forces your entire formula to run twice every time to
succeed. It would be better to know WHY you are getting the error and
perhaps test a simple piece of data instead, like:

=IF(A2<=0,0,VLOOKUP(A2,G2:H200,2,0))

That's much less intense.


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46827

Hi,
Thanks for your reply.
This is just an example i have told you about col A and col C. I actually
have a lot of code behind. My major point of interest is protecting and
unprotecting the sheets while running these functions. If i dont protect the
sheet all the formulae are running fine so its not a problem with the
formulae i have but those formulae not getting calculated when i try to
unprotect the sheet at the beginning of function and protect it again at end
of the function.

Thanks.

"JBeaucaire" wrote:


There are several solutions, it would be best to know the kind of
formula that is in COLUMN C. But a generic answer is to first test your
formula with an IF(ISERROR() test, and suppress the error message. But
those error messages are useful, so suppressing them can also suppress
an issue. Up to you to know if that is the case.

Anyway, let's take a simple formula and wrap it in an IF(ISERROR()
trap.

If your formula was something like =VLOOKUP(A2,G2:H200,2,0) and that
was causing a #VALUE error, you can trap it like so:

=IF(ISERROR(VLOOKUP(A2,G2:H200,2,0)),0,VLOOKUP(A2, G2:H200,2,0))

This works, but forces your entire formula to run twice every time to
succeed. It would be better to know WHY you are getting the error and
perhaps test a simple piece of data instead, like:

=IF(A2<=0,0,VLOOKUP(A2,G2:H200,2,0))

That's much less intense.


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46827