VLookup function problem: type mismatch 1

[JerryKing]

What’s wrong with the VLookup function my following VBscript codes? The error remind me “type mismatch”.

function searchTimeById(OpId)
Dim objExcel, excelPath, strData,
Set objExcel = CreateObject("Excel.Application")
excelPath = "Z:\OpLibrary.xls"
objExcel.Workbooks.open excelPath, false, true
Set currentWorkSheet = objExcel.ActiveWorkbook.Worksheets("Operations")

set RNG = currentWorkSheet.Range("A1:A40")
searchTimeById = currentWorkSheet.Application.VLookup(OpId, RNG, 3, FALSE)

objExcel.Workbooks.Close
objExcel.Quit
Set currentWorkSheet = Nothing
Set objExcel = Nothing
end function

sub main()
Time = searchTimeById(2088)
end sub

The excel sheet (Operations):
OpId Name Time
2088 locate 0.2
2010 drill 0.1
2050 sealing 0.05

 

[prost]

I suspect a Vlookup problem. I am not a Vlookup expert; I use it just enough to be dangerous! Nevertheless, I tried debugging your problem a little. To say Vlookup is a 'squirrely' function is an understatement. Here's what I did:
1. Typed in your data into a new spreadsheet, named the worksheet and the spreadsheet as you have named them above.
2. copied and pasted the VBA code above into a module.
3. Typed in the data you have given us in columns A to C.
4. The VBA code behavior was odd to me--I tried to get it to run with a button from the Worksheet, I get the Type Mismatch error. I then went directly to the Module in the VBA editing/project window, ran the code line by line, that appeared to work without errors. I didn't get any errors, but I also didn't get any data returned. I added a MsgBox in the subroutine, that gave me an error when I tried to just blindly print out the Time.
5. Went back to the Worksheet "Operations" and tried to use Vlookup directly in the Worksheet.
VLookup("2008",$A$1:$C$4, 3, FALSE)

That gave me an "N/A" error. I read something about desire to sort data from low to high when using Vlookup, tried that, that didn't change the error.
6. Thinking that it was having trouble figuring out what to do with the first column of data, I used the Format window to change the format from "General" to "Text". Still "N/A"
7. Then I retyped the information in the first column, still
"Text" formatting (that is, I didn't reformat to General or Number), that appeared to make VLookup("2008",$A$1:$C$4, 3, FALSE) work, returned a value of 0.2, which I suppose is what you are trying to return in the Function when you called Vlookup?

8. Back to the VBA. Hit the button to execute the Sub, that still gave me the problem. Back to line by line execution of the code. When I passed the Vlookup function, I still get the same error for when I query the value of searchTimeById after it calls Vlookup. Like a dope, without thinking, change the value of RNG from A1:A40 to A1:C40--that changed the value of searchTimeById from "Error 2023" to "0.2". If I changed RNG to A1:b40, I get Error 2023 again. (Error 2023 appears to be the Type mismatch error).

9. One more debug attempt. I put the Numbers back in the first column, that had two effects: if I used Vlookup in the Worksheet, the N/A error reappeared. However, if I ran your code with RNG set to "a1:c40" again, I got a 'Time=0.2" in my MsgBox after the function was called. Very odd behavior if you ask me.

Hope this helps.

 

[prost]

Sorry, my mistake. In my #5 above, I used "2088" in my Vlookup first argument, not "2008" as I have written.

 

[prost]

Following up: went back to your original, unsorted data, didn't care about the format of the first column, used your Sub and Function with the RNG set to A1:C40, that worked also.

 

[JerryKing]

Prost, thank you very much. Seems that the second argument of VLookup method should include the range of the value to return, i.e. the 3rd column.

 

[prost]

DOH! that makes sense, the problem was the '3' in the argument, when the 3rd column wasn't included in the VLookUp range.

 

[JerryKing]

A new problem comes up when the first argument of vLookup function is not in the table. e.g. if I put "2000" as the first argument for the above vLookup function, the error "type mismatch" comes up again. But in my opinion, the return value should be "#N/A" if there is no exact match. Prost, do you have any idea on this? Thanks.

 

[prost]

First I put the following statement, first line in your main() sub; that seemed to get rid of most problems:
Dim Time

I believe the bigger problem is your use of the variable "Time". You know that Time is an intrinsic function, that is, you don't have to declare Time using "Dim" (say Dim Time as Double), and still Excel knows what it is. To discipline myself when I write VBA, I use the statement "Option Explicit" as the first line of the module. That forces me to declare the variable type (Double, Single, Long, Integer, String, etc.) for every single variable or constant I use in the program. OK, if I use Option Explicit, I don't have to declare "Time" anywhere, VBA just knows that I am requesting a Time stamp, 01:25:35 PM for instance. I used debug to line by line, check your program's output. When I ran your program WITHOUT "Dim Time" statement above, I checked the value of Time BEFORE I dumped into the function "searchTimeById". A time stamp 12:11:39 PM appeared.

That's the source of the type mismatch error you are getting, I think. I checked by rename your Time with Time2, then executing the program. I don't get the Type mismatch error.

All of the above used "2088" in the call to the function. Now to your problem, what happens when you use "2000" in the call? I used debug again, discovered that the Vlookup call that gives you searchTimeById returns an error, Error 2042. I am guessing that Error 2042 is "#N/A", but I could not confirm that.