|
Can't Get Match Function to Use on Array
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 |
Can't Get Match Function to Use on Array
"ExcelMonkey" wrote in message
... Why is the line of code not trapping the Error and generaing a TRUE for my ISERROR statement with my current address? When you use Excel worksheet functions off the Application.WorksheetFunction object and the function being used returns an error, the WorksheetFunction object throws a VBA error. If you want the function to just return an error value that you can check, eliminate the WorksheetFunction object and just use Application.Match (you won't get autocomplete in the VB editor, but it will work just fine). -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm 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 |
Can't Get Match Function to Use on Array
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 |
Can't Get Match Function to Use on Array
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 |
Can't Get Match Function to Use on Array
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 |
Can't Get Match Function to Use on Array
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 |
| All times are GMT +1. The time now is 12:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com