Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hlookup Problem
I have a loop which loops through sheets and does things. As the loop starts
off, there is a quick test to see if the sheet name of the current sheet matches a specific name OR if it is already included in a 2D array. If either of these conditions is TRUE then nothing happens while VBA loops through it. My code is failing on the Hlookup. ?sh.Name Audit Results ?AuditShtName Audit Results Now the reason the Hlookup is failing is that the the 2D Array does not have a Audit Results in it. But sometimes it will have it. So when it has it the code will work, when it doesn't it fails. I need to wrap some error handling the Hlookup so that when it generates an error it continues. On Error Resume Next or wrap an Iserror function around it. However I still need to test the Hlookup. So I do not want an Iserror function result to overide the Hlookup test. Any ideas? For each ws in ActiveWorkbook.Worksheets If sh.Name < AuditShtName Or _ Application.HLookup(sh.Name, WorkSheetSelectForm.SheetExcludeArray, 2, False) = 0 Then 'Do something fun!!!!! End if Next Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hlookup Problem
How about just checking:
iserror(application.hlookup(...)) ExcelMonkey wrote: I have a loop which loops through sheets and does things. As the loop starts off, there is a quick test to see if the sheet name of the current sheet matches a specific name OR if it is already included in a 2D array. If either of these conditions is TRUE then nothing happens while VBA loops through it. My code is failing on the Hlookup. ?sh.Name Audit Results ?AuditShtName Audit Results Now the reason the Hlookup is failing is that the the 2D Array does not have a Audit Results in it. But sometimes it will have it. So when it has it the code will work, when it doesn't it fails. I need to wrap some error handling the Hlookup so that when it generates an error it continues. On Error Resume Next or wrap an Iserror function around it. However I still need to test the Hlookup. So I do not want an Iserror function result to overide the Hlookup test. Any ideas? For each ws in ActiveWorkbook.Worksheets If sh.Name < AuditShtName Or _ Application.HLookup(sh.Name, WorkSheetSelectForm.SheetExcludeArray, 2, False) = 0 Then 'Do something fun!!!!! End if Next Thanks -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HLOOKUP PROBLEM | Excel Worksheet Functions | |||
Problem with HLookup. Not sure if it is a bug or not | Excel Worksheet Functions | |||
hlookup problem | Excel Discussion (Misc queries) | |||
hlookup problem | Excel Discussion (Misc queries) | |||
HLookup problem with dates | Excel Discussion (Misc queries) |