vblookup and type mismatch

  • Thread starter Thread starter Merkling, Steven
  • Start date Start date
M

Merkling, Steven

That usually comes from not dimming your varables correctly

dim the variable as a variant then check its type in the watch window

HTH
-Merk

Whoops...I've been staring at the screen to long, I
think. The subject of this post should be "vlookup",
not "vblookup"
 
It is my guess that intWhichArrayRow is over 32,767 so it would have to be dimed as a double not an integer.

HTH
-Merk
Whoops...I've been staring at the screen to long, I
think. The subject of this post should be "vlookup",
not "vblookup"
 
HELP!!! I've checked everything I can think of, and I
have no idea why this isn't working. Can anyone help me?
I have a line of code that's giving me a Type Mismatch
error. It reads as follows:

intWhichArrayRow = Application.WorksheetFunction.VLookup
(strWhereGraph & strGraphNum & ".tif", varGraphCheckList,
2, False)

Using the Locals & Immediate windows, plus the
basic "hover method", I have verified that:

strWhereGraph = "Q:\1COM_ART\MAS\" (this one's a public
variable from another module)
strGraphNum = "TP0001" (String)
varGraphCheckList is a variant array (Variant(1 to 9255, 1
to 4))
varGraphCheckList(8901,1) = "Q:\1COM_ART\MAS\TP0001.tif"
(Variant/String)
varGraphCheckList(8901,2) = 8901 (Variant/Integer)
varGraphCheckList(8901,3) = 0 (Variant/Integer)
varGraphCheckList(8901,3) = "" (Variant/String)
intWhichArrayRow = 0 (Integer)
 
Whoops...I've been staring at the screen to long, I
think. The subject of this post should be "vlookup",
not "vblookup"
 
Is varGraphCheckList

An Excel Range?

-Merk
No, the results of the vlookup being assigned to
intWhichArrayRow should be = 8901

You do bring up a good point, though. I should probably
rewrite things so that things will still work in case I
ever DO search a folder structure that contains more than
32,767 files.

As for correctly dimming my variables, that was one of the
first things I checked on. I tried taking the vlookup
straight to debug.print without even going through a
variable & got the same error.
-----Original Message-----
It is my guess that intWhichArrayRow is over 32,767 so it
Click to expand...
would have to be dimed as a double not an integer.
 
No, the results of the vlookup being assigned to
intWhichArrayRow should be = 8901

You do bring up a good point, though. I should probably
rewrite things so that things will still work in case I
ever DO search a folder structure that contains more than
32,767 files.

As for correctly dimming my variables, that was one of the
first things I checked on. I tried taking the vlookup
straight to debug.print without even going through a
variable & got the same error.
-----Original Message-----
It is my guess that intWhichArrayRow is over 32,767 so it
Click to expand...
would have to be dimed as a double not an integer.
 
If there's not a match, you could get that error.

You can either ignore it and check later:

on error resume next
intWhichArrayRow = application.worksheetfunction.vlookup(....)
if err.number <> 0 then
'didn't find it, what should happen
err.clear
end if
on error goto 0

Or you could drop the .worksheetfunction and return a variant:

dim intWhichArrayRow as Variant
intwhicharrayrow = application.vlookup(....)
if iserror(intwhicharrayrow) then
'didn't find it, what should happen
end if

Either way, I'm betting you have a slight spelling difference between your
vlookup value and column 1 of your vlookup table.

(I didn't see it in your post, though.)
(tongue in check)
But I can't trust your post--you did have a typo in this line:
varGraphCheckList(8901,3) = "" (Variant/String)
I bet that ",3" should have been ",4"!
(end tongue in cheek)
 
You're right about the typos in my post. Sorry about that.
Unfortunately, though, I checked and the typos ONLY appear
in the post - not in the code or the contents of any of
the variables. Which means I still don't know why this
isn't working.
 
No, it is a variable array:

Dim varGraphCheckList() As Variant
ReDim varGraphCheckList(LBound(varGraphFileList) To UBound
(varGraphFileList), 1 To 4) As Variant
For intThisArrayRow = LBound(varGraphFileList) To UBound
(varGraphFileList)
varGraphCheckList(intThisArrayRow, 1) =
varGraphFileList(intThisArrayRow)
varGraphCheckList(intThisArrayRow, 2) = intThisArrayRow
varGraphCheckList(intThisArrayRow, 3) = 0
varGraphCheckList(intThisArrayRow, 4) = ""
Next intThisArrayRow
 
I'd check leading/trailing/embedded spaces. 0's and o's can be mixed up.
Spaces and non-breaking spaces (from copy|pasting from HTML sources).

You may want to use Chip Pearson's CellView addin to see if that helps find the
problem (I'm still betting on a difference in typing.

http://www.cpearson.com/excel/CellView.htm

===
And there are some functions that have trouble with more than 5461 elements. I
don't recall if =vlookup() is one. (and the problem goes away in xl2002, so I
can't test it out.)

Can you reduce your array to see if that gets rid of the problem?
 
-----Original Message-----
I'd check leading/trailing/embedded spaces. 0's and o's can be mixed up.
Spaces and non-breaking spaces (from copy|pasting from HTML sources).

You may want to use Chip Pearson's CellView addin to see if that helps find the
problem (I'm still betting on a difference in typing.

http://www.cpearson.com/excel/CellView.htm

===
And there are some functions that have trouble with more than 5461 elements. I
don't recall if =vlookup() is one. (and the problem goes away in xl2002, so I
can't test it out.)

Can you reduce your array to see if that gets rid of the
Click to expand...
problem?


Actually, yes. When I tried it with a much smaller
folder, it worked fine. Thanks for helping me pinpoint
the problem! I hadn't realized there was any sort of size
limit with vlookup.
Since my company doesn't seem terribly interested in
upgrading us from 97, I went ahead and wrote a function
that works much like vlookup, except that it actually
still works when you feed it a lot of information. Now
the whole macro works great! :)
 
Back
Top