+ Reply to Thread
Results 1 to 11 of 11

RowSource using Vlookup in VBA resulted Type Mismatch Error

  1. #1
    Registered User
    Join Date
    02-20-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    13

    RowSource using Vlookup in VBA resulted Type Mismatch Error

    Hi,

    I am new to the forum here and a beginner to VBA as well. I ran into some difficulties with combining VBA and Combobox:

    Basically I am trying to make a Combobox(2) dependent on the result of another Combobox (1).

    My logic is:

    1) using combobox 1 value to look up a range name
    2) set the range name as RowSource for Combobox 2

    Code is:
    Please Login or Register  to view this content.
    Excel keeps giving me "Type Mismatch Error 13". I try to Dim nameSubCategory as both "String" and "Variant" but neither works.

    Could anyone help?

    Thank you very much.

    AC
    Last edited by JosephP; 03-04-2013 at 12:56 PM. Reason: add code tags

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: RowSource using Vlookup in VBA resulted Type Mismatch Error

    I reckon your vlookup is probably returning an error
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Registered User
    Join Date
    02-20-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: RowSource using Vlookup in VBA resulted Type Mismatch Error

    The weird thing is, the code actually works by itself , that means I can select an item from the drop down list from Combobox 1 and Combobox 2 populates itself with no problem. It is when I execute another code (to export data), the Excel gives me the "run time error 13 type mismatch" message. Another weird thing is, even the second code works fine, that means data was perfectly exported. I just keep seeing this error message.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,583

    Re: RowSource using Vlookup in VBA resulted Type Mismatch Error

    Sounds like the Change event you posted is somehow being triggered by this other code and when that happens the VLookUp fails.

    Which line of code is highlighted when you get the error?
    If posting code please use code tags, see here.

  5. #5
    Registered User
    Join Date
    02-20-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: RowSource using Vlookup in VBA resulted Type Mismatch Error

    When "Dim nameSubCategory As Variant"

    It's this line:

    .RowSource = nameSubCategory

    When "Dim nameSubCategory As String"

    It's this line:

    nameSubCategory = Application.VLookup(valCategory, QFCNames, 3, False)

    Thank you!

  6. #6
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: RowSource using Vlookup in VBA resulted Type Mismatch Error

    that means the lookup value is not being found

  7. #7
    Registered User
    Join Date
    02-20-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: RowSource using Vlookup in VBA resulted Type Mismatch Error

    But when I used "MsgBox nameSubCategory" to see if the vlookup works it does return the right answer?

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: RowSource using Vlookup in VBA resulted Type Mismatch Error

    without a workbook I can't tell you exactly why but that's the only explanation for those symptoms

  9. #9
    Registered User
    Join Date
    02-20-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: RowSource using Vlookup in VBA resulted Type Mismatch Error

    Hi JP, here is the file.

    Please click the button on the first tab and go to the very first tab (Quality Failure)

    The code is for the Category combobox.

    Thank you!
    Attached Files Attached Files

  10. #10
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: RowSource using Vlookup in VBA resulted Type Mismatch Error

    I'm in meetings for the rest of the day but will try and have a look tonight

  11. #11
    Registered User
    Join Date
    02-20-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: RowSource using Vlookup in VBA resulted Type Mismatch Error

    hey guys it's solved.

    All it takes is to add:

    If cboCategory.Value <> "" Then

    Code

    End If

    The reason is the code actually clean the form before the next step (the "" value).

    Thanks for all your help JP and Norie!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1