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

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