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

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

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






"coco" wrote:

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

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

"JMB" wrote:

> 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".
>
>
>
>
>
>
> "coco" wrote:
>
> > 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
> >
> >

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
"JMB" <[email protected]> wrote in message
news:[email protected]...
>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".
>
>
>
>
>
>
> "coco" wrote:
>
>> 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
>>
>>

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?

"coco" wrote:

> 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
>
> "JMB" wrote:
>
> > 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".
> >
> >
> >
> >
> >
> >
> > "coco" wrote:
> >
> > > 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
> > >
> > >

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

Thanks

Coco


"JMB" wrote:

> 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?
>
> "coco" wrote:
>
> > 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
> >
> > "JMB" wrote:
> >
> > > 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".
> > >
> > >
> > >
> > >
> > >
> > >
> > > "coco" wrote:
> > >
> > > > 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
> > > >
> > > >