Why does Match statement in VBA fail on cells with errors in them.

[ExcelMonkey]

I have a line of code only that only fails when the public variable "cell"
has an error
statement in it (i.e. Div/0, REF# #####, NAME! etc). The variable is
dimensioned as a Range.

As I am simply pulling the variables address proptery and Parent Name into
the Match, why would these error statements matter? The idea below is that
if the Match fails, i error handle it with the ISERROR sttm. Yet the actual
cell errors in the spreadsheet itself seem to make this line of code fail.
Why is that? Thanks

Not IsError(Application.Match(cell.Parent.Name & "!" & cell.Address,
UniqueCellAddressArray3, 0) - 1)

[Bernie Deitrick]

When Excel evaluates

Match(cell.Parent.Name & "!" & cell.Address

it becomes

Match(error value

Try catching the error sooner:

If Not IsError(cell.value) then
.....

HTH,
Bernie
MS Excel MVP


"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
>I have a line of code only that only fails when the public variable "cell"
> has an error
> statement in it (i.e. Div/0, REF# #####, NAME! etc). The variable is
> dimensioned as a Range.
>
> As I am simply pulling the variables address proptery and Parent Name into
> the Match, why would these error statements matter? The idea below is that
> if the Match fails, i error handle it with the ISERROR sttm. Yet the actual
> cell errors in the spreadsheet itself seem to make this line of code fail.
> Why is that? Thanks
>
> Not IsError(Application.Match(cell.Parent.Name & "!" & cell.Address,
> UniqueCellAddressArray3, 0) - 1)
>

[ExcelMonkey]

Why would the match fail Bernie. The array holds string data (i.e. cell
addresses). The lookup term in the match is nothing more than the address
and sheet name properties of the public range variable "cell". So althought
the value in the cell in Excel is technically an error, I am not using the
value of the cell in the Match term. Or is it that I am in fact still using
it by virtue of its inclusion in the Match irrespective of is propteries?

"Bernie Deitrick" wrote:

> When Excel evaluates
>
> Match(cell.Parent.Name & "!" & cell.Address
>
> it becomes
>
> Match(error value
>
> Try catching the error sooner:
>
> If Not IsError(cell.value) then
> .....
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "ExcelMonkey" <[email protected]> wrote in message
> news:[email protected]...
> >I have a line of code only that only fails when the public variable "cell"
> > has an error
> > statement in it (i.e. Div/0, REF# #####, NAME! etc). The variable is
> > dimensioned as a Range.
> >
> > As I am simply pulling the variables address proptery and Parent Name into
> > the Match, why would these error statements matter? The idea below is that
> > if the Match fails, i error handle it with the ISERROR sttm. Yet the actual
> > cell errors in the spreadsheet itself seem to make this line of code fail.
> > Why is that? Thanks
> >
> > Not IsError(Application.Match(cell.Parent.Name & "!" & cell.Address,
> > UniqueCellAddressArray3, 0) - 1)
> >
>
>
>

[ExcelMonkey]

Actually, this line of code is failing on everthing not just error statements
in cells. Its a type 13 Error Mismatch. I posted this earlier today. When
i got to the Immediate Window, I can see that the array has values in it and
also that the match search term can be recognized.

Immediate Window
?cell.Parent.Name & "!" & cell.Address
Error Messages!$B$4

?UniqueCellAddressArray3(0)
Unique Formulas!$C$2

Public Cell As Range

Private Sub CheckAddressAgainstUniquelist()
If UniqueFormulasAdjustChkBx = True Then
If Not IsError(Application.Match(Cell.Parent.Name & "!" & Cell.Address,
UniqueCellAddressArray3, 0) - 1) Then
Flag = False
Else
Flag = True
End If
Else
Flag = False
End If
End Sub




"Bernie Deitrick" wrote:

> When Excel evaluates
>
> Match(cell.Parent.Name & "!" & cell.Address
>
> it becomes
>
> Match(error value
>
> Try catching the error sooner:
>
> If Not IsError(cell.value) then
> .....
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "ExcelMonkey" <[email protected]> wrote in message
> news:[email protected]...
> >I have a line of code only that only fails when the public variable "cell"
> > has an error
> > statement in it (i.e. Div/0, REF# #####, NAME! etc). The variable is
> > dimensioned as a Range.
> >
> > As I am simply pulling the variables address proptery and Parent Name into
> > the Match, why would these error statements matter? The idea below is that
> > if the Match fails, i error handle it with the ISERROR sttm. Yet the actual
> > cell errors in the spreadsheet itself seem to make this line of code fail.
> > Why is that? Thanks
> >
> > Not IsError(Application.Match(cell.Parent.Name & "!" & cell.Address,
> > UniqueCellAddressArray3, 0) - 1)
> >
>
>
>

[Dave Peterson]

You've got another response at one of your other threads.

ExcelMonkey wrote:
>
> I have a line of code only that only fails when the public variable "cell"
> has an error
> statement in it (i.e. Div/0, REF# #####, NAME! etc). The variable is
> dimensioned as a Range.
>
> As I am simply pulling the variables address proptery and Parent Name into
> the Match, why would these error statements matter? The idea below is that
> if the Match fails, i error handle it with the ISERROR sttm. Yet the actual
> cell errors in the spreadsheet itself seem to make this line of code fail.
> Why is that? Thanks
>
> Not IsError(Application.Match(cell.Parent.Name & "!" & cell.Address,
> UniqueCellAddressArray3, 0) - 1)

--

Dave Peterson

[Tom Ogilvy]

You are trying to subtract 1 before you determine if it is an error value
being returned. For the test you are conducting, you don't need to subtract
1



If Not IsError(Application.Match(Cell.Parent.Name _
& "!" & Cell.Address, UniqueCellAddressArray3, 0)) Then

should work

--
Regards,
Tom Ogilvy


"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> Actually, this line of code is failing on everthing not just error
statements
> in cells. Its a type 13 Error Mismatch. I posted this earlier today.
When
> i got to the Immediate Window, I can see that the array has values in it
and
> also that the match search term can be recognized.
>
> Immediate Window
> ?cell.Parent.Name & "!" & cell.Address
> Error Messages!$B$4
>
> ?UniqueCellAddressArray3(0)
> Unique Formulas!$C$2
>
> Public Cell As Range
>
> Private Sub CheckAddressAgainstUniquelist()
> If UniqueFormulasAdjustChkBx = True Then
> If Not IsError(Application.Match(Cell.Parent.Name & "!" &
Cell.Address,
> UniqueCellAddressArray3, 0) - 1) Then
> Flag = False
> Else
> Flag = True
> End If
> Else
> Flag = False
> End If
> End Sub
>
>
>
>
> "Bernie Deitrick" wrote:
>
> > When Excel evaluates
> >
> > Match(cell.Parent.Name & "!" & cell.Address
> >
> > it becomes
> >
> > Match(error value
> >
> > Try catching the error sooner:
> >
> > If Not IsError(cell.value) then
> > .....
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "ExcelMonkey" <[email protected]> wrote in message
> > news:[email protected]...
> > >I have a line of code only that only fails when the public variable
"cell"
> > > has an error
> > > statement in it (i.e. Div/0, REF# #####, NAME! etc). The variable is
> > > dimensioned as a Range.
> > >
> > > As I am simply pulling the variables address proptery and Parent Name
into
> > > the Match, why would these error statements matter? The idea below is
that
> > > if the Match fails, i error handle it with the ISERROR sttm. Yet the
actual
> > > cell errors in the spreadsheet itself seem to make this line of code
fail.
> > > Why is that? Thanks
> > >
> > > Not IsError(Application.Match(cell.Parent.Name & "!" & cell.Address,
> > > UniqueCellAddressArray3, 0) - 1)
> > >
> >
> >
> >