I think I'd break that subtraction out of the line. If it returns an error,
then subtracting 1 will cause another error.

dim res as variant
res = application.match(cell.parent.name & "!" & cell.address, _
uniquecelladdressarray3,0)
if iserror(res) then
'missing
else
msgbox res - 1
'or what ever you're doing with that result
end if

======
Ps. If you're trying to keep track of the addresses, you could store this kind
of format into that array:

cell.address(external:=true)
it'll return something like:
[book1.xls]Sheet5!$A$1
But it has the sheet built into it and you don't have to assemble the pieces.

(You'll have to adjust what you match on and how you build that
uniquecelladdressarray, though.)



ExcelMonkey wrote:

So Dave this line of code only fails when the variable "cell" has an error
statement in it (i.e. Div/0, REF# #####, NAME! etc). As I am simply pulling
the variables address proptery and Parent Name into the Match, why would
these error statements matter? I actually want the errors to be there. How
do I wrap another error handler around this to accomodate both the Excel
errors in the actual spreadshet AND the error that will occur when the Match
fails in VBA? Thanks

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

"ExcelMonkey" wrote:

Rob's point is well taken. I have stopped passing it to the function as it
is public variable and I do not need to do this. I still have the variable
"cell" delcared publically. I then simply call a sub routine called
CheckAddressAgainstUniquelist. This then attempts to check the public
variables cell address against those stored in the array. I have also
dropped the "worksheetfunction" after Application in the called sub. I am
now getting Run Time Error 13 Type Mismatch when it starts to run the ISERROR
stmt. Note that Immediate window shows that there are values in the array
and the search term in the Match also shows up in my immediate window as
well. What am I doing wrong here. Thanks

Public Cell As Range

Call CheckAddressAgainstUniquelist
If AdjustForUniqueFormula = True Then
Exit Sub
Else
Do something....
End if

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



"Dave Peterson" wrote:

What happens if you drop the .worksheetfunction and just use:

If Not IsError(Application.Match(....

or even:
If isnumeric(Application.Match(



ExcelMonkey wrote:

esI have loaded a bunch of cell address into a 1D array. I am now checking a
current cell address to see if it is in the array of preloaded address using
a MATCH function. If it is not in the array the ISERROR function wrapped
around the MATCH should be TRUE. The line of code below is failing with a
1004 Error ("Unable to get Match property of the worksheet function class"

If Not IsError(Application.WorksheetFunction.Match(cell.P arent.Name &
"!" & cell.Address, UniqueCellAddressArray3, 0) - 1) Then
'Do something.......................
Else

When I check in my immediate window I get:
?cell.Parent.Name & "!" & cell.Address
Colour Legend!$E$2

I have also checked the array and it is in fact loaded with data:

?UniqueCellAddressArray3(1)
Unique Formulas!$D$2

And when I check the value that I know exists in the array I get an answer:

?Application.WorksheetFunction.Match("Unique Formulas!$D$2",
UniqueCellAddressArray3, 0) - 1
1

Why is the line of code not trapping the Error and generaing a TRUE for my
ISERROR statement with my current address?

Thanks

--

Dave Peterson


--

Dave Peterson