VLookup function problem: type mismatch
VLookup function problem: type mismatch
(OP)
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
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





RE: VLookup function problem: type mismatch
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.
RE: VLookup function problem: type mismatch
RE: VLookup function problem: type mismatch
RE: VLookup function problem: type mismatch
RE: VLookup function problem: type mismatch
RE: VLookup function problem: type mismatch
RE: VLookup function problem: type mismatch
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.