Error Handling to mitigate "Run Time Erorr 13 Type Mismatch"

[ExcelMonkey]

I have a line of code that is failing due to a Run Time Error 13 Type
Mismatch. The code is as following:

If Application.HLookup(sh.Name, WorkSheetSelectForm.SheetExcludeArray, 2,
False) = 0 Then

Now I know why its failing. The term sh.Name does not exisit in the Array
that I am performing the Hlookup on. However, I need to warp error handling
around this so that the code will progress. I have tried wrapping a ISERROR
stmt around it to generate a boolean as a start. But I cannot get this to
work. When I do the following below in the Immediate Window I get a "Run
Time Error 1001Unable to get the Hlookup Property of the Worksheet Function
class"

?ISERROR(Application.worksheetfunction.HLookup(sh.Name,
WorkSheetSelectForm.SheetExcludeArray, 2, False) = 0)

What can I do in order to error handle this?

Thanks

[Dave Peterson]

dim res as variant
res = application.hlookup(sh.Name, _
WorkSheetSelectForm.SheetExcludeArray, 2, False)

if iserror(res) then
'not found
else
'it was found
if res = 0 then
'your code here
end if
end if

application.worksheetfunction.hlookup causes a trappable error.

dim res as variant
on error resume next
res = application.worksheetfunction.hlookup(....)
if err.number <> 0 then
'not a match, the board goes back
err.clear
else
if res = 0 then
'your code here
end if
end if

I find the first way (application.hlookup()) easier to write and easier to read.



ExcelMonkey wrote:
>
> I have a line of code that is failing due to a Run Time Error 13 Type
> Mismatch. The code is as following:
>
> If Application.HLookup(sh.Name, WorkSheetSelectForm.SheetExcludeArray, 2,
> False) = 0 Then
>
> Now I know why its failing. The term sh.Name does not exisit in the Array
> that I am performing the Hlookup on. However, I need to warp error handling
> around this so that the code will progress. I have tried wrapping a ISERROR
> stmt around it to generate a boolean as a start. But I cannot get this to
> work. When I do the following below in the Immediate Window I get a "Run
> Time Error 1001Unable to get the Hlookup Property of the Worksheet Function
> class"
>
> ?ISERROR(Application.worksheetfunction.HLookup(sh.Name,
> WorkSheetSelectForm.SheetExcludeArray, 2, False) = 0)
>
> What can I do in order to error handle this?
>
> Thanks

--

Dave Peterson

[Eric White]

On Error Resume Next
If Application.HLookup(sh.Name, WorkSheetSelectForm.SheetExcludeArray, 2,
False) = 0 Then
Select Case Err.Number
Case 0
'Action to do if there is no error
Case 13
'Action to do if sh.Name does not exist in array

'Case... i.e., any other errors you need to trap

Case Else
'Any unexpected/unhandled errors
End Select
On Error Goto 0 'Turn auto error control back on


"ExcelMonkey" wrote:

> I have a line of code that is failing due to a Run Time Error 13 Type
> Mismatch. The code is as following:
>
> If Application.HLookup(sh.Name, WorkSheetSelectForm.SheetExcludeArray, 2,
> False) = 0 Then
>
> Now I know why its failing. The term sh.Name does not exisit in the Array
> that I am performing the Hlookup on. However, I need to warp error handling
> around this so that the code will progress. I have tried wrapping a ISERROR
> stmt around it to generate a boolean as a start. But I cannot get this to
> work. When I do the following below in the Immediate Window I get a "Run
> Time Error 1001Unable to get the Hlookup Property of the Worksheet Function
> class"
>
> ?ISERROR(Application.worksheetfunction.HLookup(sh.Name,
> WorkSheetSelectForm.SheetExcludeArray, 2, False) = 0)
>
> What can I do in order to error handle this?
>
> Thanks
>

[Bob Phillips]

Try something like

On Error Resume Next
iNum = Application.HLookup(sh.Name,
WorkSheetSelectForm.SheetExcludeArray, 2, False)
On Error Goto 0
If iNum <> 0 Then
'...
--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> I have a line of code that is failing due to a Run Time Error 13 Type
> Mismatch. The code is as following:
>
> If Application.HLookup(sh.Name, WorkSheetSelectForm.SheetExcludeArray, 2,
> False) = 0 Then
>
> Now I know why its failing. The term sh.Name does not exisit in the Array
> that I am performing the Hlookup on. However, I need to warp error
handling
> around this so that the code will progress. I have tried wrapping a
ISERROR
> stmt around it to generate a boolean as a start. But I cannot get this to
> work. When I do the following below in the Immediate Window I get a "Run
> Time Error 1001Unable to get the Hlookup Property of the Worksheet
Function
> class"
>
> ?ISERROR(Application.worksheetfunction.HLookup(sh.Name,
> WorkSheetSelectForm.SheetExcludeArray, 2, False) = 0)
>
> What can I do in order to error handle this?
>
> Thanks
>