+ Reply to Thread
Results 1 to 21 of 21

Matching values of String type

  1. #1
    Registered User
    Join Date
    01-05-2019
    Location
    singappre
    MS-Off Ver
    2016
    Posts
    8

    Matching values of String type

    hi guys im rather new to excel and i've been trying to create a simple user form according to this video on youtube "Data Entry Form In VBA (PART-3)"
    however, i've been getting the error "type mismatch" when i try to use application.match to match with a string value as the row i'm trying to match with is of string value.
    converting it to Long type works for values with only numbers in them, i have to match with values that are a combination of alphabets, numbers, symbols like dash and backslash.
    if i were to use the variant type how would i go about doing it?
    please advise on how to match string or variant values , thank you.

    Please Login or Register  to view this content.
    Attached Images Attached Images
    Last edited by AliGW; 01-09-2019 at 01:54 AM. Reason: Irrelevant section of thread title removed.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Please help: (Matching values of String type)

    Hi Chris and welcome.

    We are here to help you. However you must conply with the forum rules.

    Also, we don't work from images. Attach a sample Spreadsheet.

    Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Last edited by AliGW; 01-08-2019 at 06:20 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Please help: (Matching values of String type)

    Hi chrischris369
    Welcome to the Forum.
    In future please view the forum rules before posting. Your post does not comply with rule #2.
    Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing.
    So, do the following:
    Edit your post, highlight your code and click the [#] button at the top of the post window.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Please help: (Matching values of String type)

    As Chris is a new member, I have added the code tags for him this time.

    Chris - next time you must do this yourself, please.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Please help: (Matching values of String type)

    This should work


    Please Login or Register  to view this content.

  6. #6
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Please help: (Matching values of String type)

    Hi Chrischris269

    Declaring i as long works...With what entry does your code error...

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Please help: (Matching values of String type)

    Same person?

    VBA Application.Match bug

    Variable i should be variant.
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    01-05-2019
    Location
    singappre
    MS-Off Ver
    2016
    Posts
    8

    Re: Please help: (Matching values of String type)

    hello guys, thanks for your replies and help. sorry that i made so many mistakes making this post haha.
    i have attached my workbook. can you guys please try the update form so that you can see the error?
    thank youuu
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    01-05-2019
    Location
    singappre
    MS-Off Ver
    2016
    Posts
    8

    Re: Please help: (Matching values of String type)

    i've tried it however, i receive another type mismatch error for another matching function further below.
    can you check the workbook i attached and help me out?

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Matching values of String type

    Why didn't you change as I suggested???

    Do you want me to change whole codes completely?

  11. #11
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Matching values of String type

    this works for me...

    Please Login or Register  to view this content.

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Matching values of String type

    No used of Match method.
    I would do like this.
    Please Login or Register  to view this content.

  13. #13
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Matching values of String type

    1. I do not see your problem. My code works. Workbook attached.



    2. Having Said that, the Combobox is not a scalable solution for your requirement.
    A faster and more practical solution is to use two or more text boxes.

    I will modify your Userform to Show you how it should be done.

    I replaced the Combobox with two text boxes
    I also added a List Box

    Type "hp " into the green text box

    Then type "fan" into the yellow text box

    finally select an entry in the list box.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by mehmetcik; 01-09-2019 at 01:29 PM.

  14. #14
    Registered User
    Join Date
    01-05-2019
    Location
    singappre
    MS-Off Ver
    2016
    Posts
    8

    Re: Matching values of String type

    hello jindon i referred to ur previous comment and from that thread u linked i tried messing around by changing some of the data types and indeed it does work thank you so much for your help.
    in the codes that you pasted could you explain to me why the range of i is only 2 to 5? im having some trouble understanding

  15. #15
    Registered User
    Join Date
    01-05-2019
    Location
    singappre
    MS-Off Ver
    2016
    Posts
    8

    Re: Matching values of String type

    hi mehmetcik,
    i like the modifications u made to the code and i think that it is useful and more convenient that my current solution.
    however, im having some trouble understanding the code...
    also u mentioned scalability of the current code, do you mean that when there a more rows of data entered there will be a possibility of the userform failing?

  16. #16
    Registered User
    Join Date
    01-05-2019
    Location
    singappre
    MS-Off Ver
    2016
    Posts
    8

    Re: Matching values of String type

    hi sintek,
    "Dim i As Long" does not work for me, i keep receiving Compile Error: Syntax Error on the "Private Sub ComboBox1_Change()" line.

    Please Login or Register  to view this content.

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Matching values of String type

    If you don't even test my code, ignore my code.

  18. #18
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Matching values of String type

    1. Scalability.

    If you have 1000 rows of data then you will have 1000 items in the combobox. So it will take ages to find the right line.
    My way is a lot faster.

    2. Understanding the Code.

    I have added notes:-

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by mehmetcik; 01-10-2019 at 02:12 PM.

  19. #19
    Registered User
    Join Date
    01-05-2019
    Location
    singappre
    MS-Off Ver
    2016
    Posts
    8

    Re: Matching values of String type

    Quote Originally Posted by jindon View Post
    If you don't even test my code, ignore my code.
    i really appreciate u taking the time to help fix my issue, but i can't test it if i dont understand, i have to understand the code as i need to explain it. no need to get all offended and become passive-aggressive
    Last edited by chrischris369; 01-12-2019 at 10:47 AM.

  20. #20
    Registered User
    Join Date
    01-05-2019
    Location
    singappre
    MS-Off Ver
    2016
    Posts
    8

    Re: Matching values of String type

    Quote Originally Posted by mehmetcik View Post
    1. Scalability.

    If you have 1000 rows of data then you will have 1000 items in the combobox. So it will take ages to find the right line.
    My way is a lot faster.

    2. Understanding the Code.

    I have added notes:-

    Please Login or Register  to view this content.
    Thank u so much for taking the time to explain your code. i will try to understand it hahaha.

  21. #21
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Matching values of String type

    Feel free to come back to me at any time.

    That is the only way to learn.
    Attached Files Attached Files
    Last edited by mehmetcik; 01-12-2019 at 06:56 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Only text values matching using index/match lookup - data type pro
    By Stanton in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 PM
  2. Only text values matching using index/match lookup - data type pro
    By Stanton in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 PM
  3. [SOLVED] Only text values matching using index/match lookup - data type pro
    By Stanton in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  4. Only text values matching using index/match lookup - data type pro
    By Dave Peterson in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 07:05 AM
  5. [SOLVED] Only text values matching using index/match lookup - data type pro
    By Dave Peterson in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 04:05 AM
  6. [SOLVED] Only text values matching using index/match lookup - data type pro
    By Stanton in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  7. Only text values matching using index/match lookup - data type pro
    By Stanton in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  8. [SOLVED] Only text values matching using index/match lookup - data type pro
    By Stanton in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-01-2005, 09:05 PM

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