Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vblookup and type mismatch
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 11/25/03 03:39PM Whoops...I've been staring at the screen to long, I think. The subject of this post should be "vlookup", not "vblookup" -----Original Message----- 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) . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vblookup and type mismatch
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 11/25/03 03:39PM Whoops...I've been staring at the screen to long, I think. The subject of this post should be "vlookup", not "vblookup" -----Original Message----- 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) . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vblookup and type mismatch
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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vblookup and type mismatch
Whoops...I've been staring at the screen to long, I
think. The subject of this post should be "vlookup", not "vblookup" -----Original Message----- 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) . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
vblookup and type mismatch
Is varGraphCheckList
An Excel Range? -Merk 11/25/03 04:54PM 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 would have to be dimed as a double not an integer. HTH -Merk 11/25/03 03:39PM Whoops...I've been staring at the screen to long, I think. The subject of this post should be "vlookup", not "vblookup" -----Original Message----- 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) . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
vblookup and type mismatch
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 would have to be dimed as a double not an integer. HTH -Merk 11/25/03 03:39PM Whoops...I've been staring at the screen to long, I think. The subject of this post should be "vlookup", not "vblookup" -----Original Message----- 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) . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
vblookup and type mismatch
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) Janelle wrote: 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) -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
vblookup and type mismatch
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. -----Original Message----- 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) Janelle wrote: 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) -- Dave Peterson . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
vblookup and type mismatch
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 -----Original Message----- Is varGraphCheckList An Excel Range? -Merk 11/25/03 04:54PM 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 would have to be dimed as a double not an integer. HTH -Merk 11/25/03 03:39PM Whoops...I've been staring at the screen to long, I think. The subject of this post should be "vlookup", not "vblookup" -----Original Message----- 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) . . . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
vblookup and type mismatch
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? Janelle wrote: 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. -----Original Message----- 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) Janelle wrote: 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) -- Dave Peterson . -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
vblookup and type mismatch
-----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 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! :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Type Mismatch Error | Excel Discussion (Misc queries) | |||
Type mismatch... | Excel Discussion (Misc queries) | |||
Type Mismatch | Excel Worksheet Functions | |||
type mismatch--how to fix | Excel Discussion (Misc queries) |