"if, IsError,Cell & Formulas";giving error message when cell is #R

[Guest]

I have question regarding "if", "IsError" and a specific error message "REF",
because it does not execute correctly:

For example this is my function button:

***************************
Private Sub btnSaveNext_Click()
Dim EReturnValue As Boolean

EReturnValue = IsError(Hoja(jCopy).Cells(xCopy, yCopy).Formula)

If EReturnValue Then
MsgBox "Not able to update the cell, error will occur, check values again"
Else
Hoja(jCopy).Cells(xCopy, yCopy).Formula = TxtNCellValue.Value
btnSaveNext.Enabled = False
btnCopyCellFromCurrent.Enabled = False
End If

End Sub

***************************



I already checked the value of:
Hoja(jCopy).Cells(xCopy, yCopy).Formula
And it is:

=Sheet2!C288+#REF!C288

With other kind of values not including "#REF!" the function works fine


I know that "#REF!" is and ERROR and I want to "SKIP it" with a Message Box:
"Not able to update the cell, error will occur, check values again"

But it does not go to this message
Instead of that, it crash the macro (showing me in a yellow line after
"Else" )
Also, it shows this error message:

******************************
Microsoft Visual Basic
Run-time error '1004';
Application-defined or object-defined error
******************************


Thanks for your help

Coco

 

[Guest]

I think this line will return true even if there is an error in one of the
formula elements (as formula returns a string - correct?). Maybe check the
Value of the cell for an error.

EReturnValue = IsError(Hoja(jCopy).Cells(xCopy, yCopy).Formula)

As far as the error you're getting after the else, what is TxtNCellValue? I
don't see it defined anywhere. Is it a control on the userform (in which
case, try "Me.TxtNCellValue.Value".

 

[Guest]

The formula "EReturnValue" works fine when the cell has a format of "value or
formula"

EReturnValue = IsError(Hoja(jCopy).Cells(xCopy, yCopy).Formula)

The problems happens when the cell in that particular location contains an
error message (or I think an inconsistence result of "formula")like #REF!
in this case:

=Sheet2!C288+#REF!C288

My question is, if is there a way to force the macro to ask the user who
runs it, to let him check the cell, with a MsgBox that says:

msgBox "Check cell ??? because it can not be processed"

and then let him close and end the macro, and not going to a "debug error"
as mentioned before?

Thanks

Coco

 

[William Benson]

As for getting excel to halt and taking you to a line highlighted in yellow,
this could be because you have error trapping set to break in code module...

In the Visual Basic Editor, click Tools-Options-General Tab, on right hand
side is where you tell VBA how to handle errors.

Since you have an error handler, I suggest "Break on Unhandled Errors" as
the appropriate setting.

HTH ... Bill

 

[Guest]

just to be sure we're talking about the same thing, i was asking about changing

EReturnValue = IsError(Hoja(jCopy).Cells(xCopy, yCopy).Formula)
to
EReturnValue = IsError(Hoja(jCopy).Cells(xCopy, yCopy).value)

because I tried a small macro w/following line of code

msgbox iserror(activecell.formula)

where the formula in the activecell is =3+5+#REF! and got a return value of
FALSE, but when i use .Value, I get TRUE. i thought maybe that's why it is
not processing your message box to check the cell value.

your code compiles OK?

 

[Guest]

JMB,
You were right.
I changed from "Formula" to "Value" and it works fine now

Thanks

Coco