Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling to mitigate "Run Time Erorr 13 Type Mismatch"
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling to mitigate "Run Time Erorr 13 Type Mismatch"
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling to mitigate "Run Time Erorr 13 Type Mismatch"
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling to mitigate "Run Time Erorr 13 Type Mismatch"
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"ByRef argument type mismatch" Error | Excel Programming | |||
Run-time Error "13" - File Type Mismatch | Excel Programming | |||
Help with Run-time error: "Type Mismatch" | Excel Programming | |||
"FIND" generates "Type mismatch" error | Excel Programming | |||
Copying data to another worksheet gives "Type Mismatch" error | Excel Programming |