Vlookup of VBA Array
 

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,Shee tExcludeArray,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

Vlookup of VBA Array
 

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"

--
Regards,
Tom Ogilvy


"ExcelMonkey" wrote in message
m...
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,Shee tExcludeArray,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

Vlookup of VBA Array
 

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,Shee tExcludeAr*ray,2,0)

?SheetEXcludeArray (0,1)
Advanced Output

? SheetEXcludeArray (1,1)
1

?sh.Name
Advanced Outptut





Tom Ogilvy wrote:
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"

--
Regards,
Tom Ogilvy


"ExcelMonkey" wrote in message
m...
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,Shee tExcludeArray,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

Vlookup of VBA Array
 

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





"Tom Ogilvy" wrote in message ...
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"

--
Regards,
Tom Ogilvy


"ExcelMonkey" wrote in message
m...
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,Shee tExcludeArray,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

Vlookup of VBA Array
 

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

wrote:

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,Shee tExcludeAr*ray,2,0)

?SheetEXcludeArray (0,1)
Advanced Output

? SheetEXcludeArray (1,1)
1

?sh.Name
Advanced Outptut

Tom Ogilvy wrote:
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"

--
Regards,
Tom Ogilvy


"ExcelMonkey" wrote in message
m...
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,Shee tExcludeArray,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

--

Dave Peterson