×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

VLookup function problem: type mismatch

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

RE: VLookup function problem: type mismatch

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.

RE: VLookup function problem: type mismatch

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

RE: VLookup function problem: type mismatch

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.

RE: VLookup function problem: type mismatch

(OP)
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. :)

RE: VLookup function problem: type mismatch

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

RE: VLookup function problem: type mismatch

(OP)
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.

RE: VLookup function problem: type mismatch

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources