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

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)

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

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" wrote in message
...
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)

Why does Match statement in VBA fail on cells with errors in t
 

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" wrote in message
...
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)

Why does Match statement in VBA fail on cells with errors in t
 

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" wrote in message
...
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)

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

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

Why does Match statement in VBA fail on cells with errors in t
 

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" wrote in message
...
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" wrote in message
...
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)