Vlookup of VBA Array

[ExcelMonkey]

I am trying to do a Vlookup on a 2D VBA array that I created. The
array named SheetExcludeArray has been defined as a Public variable.
I have loaded sheet names into the first column of the array and
values in the other column. Later on in my routine, I want to
retrieve the values in the second column. As follows:

Sheet 1 1
Advanced Ouput 1
Sheet 3 0

I am gettng a 1004 Error stating "Unable to get Vlookp property of the
Worksheet Function".

The line of code I am using is:
Application.WorksheetFunction.Vlookup(sh.Name,SheetExcludeArray,2,0)

However when I go to the Immediate Window I get this:

?SheetEXcludeArray (0,1)
Advanced Output

? SheetEXcludeArray (1,1)
1

?sh.Name
Advanced Ouptut

What doest the 1004 error imply? My immediate window tells me the
array has values in it and that my variable has a sheet name passed to
it. What could be wrong here?

Thanks

 

[Tom Ogilvy]

That means the value wasn't found.

Look closely at your posting. the two things you are comparing are not
equal

?SheetEXcludeArray (0,1)
Advanced Output

?sh.Name
Advanced Ouptut

"Advanced Ouptut" <> "Advanced Output"

 

[excelmonkey]

I am gettng a 1004 Error stating "Unable to get Vlookp property of the
Worksheet Function".

Sorry Tom. I was retyping from the Immediate Window on my laptop. The
two texts strings area exactly identical. As they should be as. Given
this,whey is this Vlookup failing? The array has data in it. The
lookup variable has text string passed to it. And we can tell that the
textstring actually exists in the array. And we know that the other
item in column 2 is there as well. Whey won't this work?

The line of code I am using is:
Application.WorksheetFunction.Vlookup(sh.Name,SheetExcludeAr­ray,2,0)

?SheetEXcludeArray (0,1)
Advanced Output

? SheetEXcludeArray (1,1)
1

?sh.Name
Advanced Outptut

 

[ExcelMonkey]

I think it may be that I need an Hlookup instead of a Vlookup. As I
am looking to get the column item. That should fix it. Sorry about
that.

Thanks

 

[Dave Peterson]

Not quite identical:
Advanced Output
Advanced Outptut
*******