#value error

S

swtupr

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.
 
J

JE McGimpsey

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

JBeaucaire

There are several solutions, it would be best to know the kind o
formula that is in COLUMN C. But a generic answer is to first test you
formula with an IF(ISERROR() test, and suppress the error message. Bu
those error messages are useful, so suppressing them can also suppres
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 tha
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 t
succeed. It would be better to know WHY you are getting the error an
perhaps test a simple piece of data instead, like

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

That's much less intense
 
S

swtupr

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Loop Through Text and Test for Scenarios Q 31
A For Each in Range... with a .Find(what:="TJ", ... in it problem 15
Loop thru xx No. of sheets until the four strValue combo's are found 6
Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to a list onsame & another sheet 27
Six columns randon no.'s no dupes 6
VLookup using VB 5
3 results possible on every day. add up the number of each on each day. 2
VBA to run all variable combinations in a calculation model and store result 6

Top