Matching values of String type

[chrischris369]

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.

[mehmetcik]

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.

[Sintek]

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.

[AliGW]

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.

[mehmetcik]

This should work

Please Login or Register  to view this content.

[Sintek]

Hi Chrischris269

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

[jindon]

Same person?

VBA Application.Match bug

Variable i should be variant.

Please Login or Register  to view this content.

[chrischris369]

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

[chrischris369]

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?

[jindon]

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

Do you want me to change whole codes completely?

[Sintek]

this works for me...

Please Login or Register  to view this content.

[jindon]

No used of Match method.
I would do like this.

Please Login or Register  to view this content.

[mehmetcik]

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.

[chrischris369]

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

[chrischris369]

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?

[chrischris369]

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.

[jindon]

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

[mehmetcik]

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.

[chrischris369]

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

[chrischris369]

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.

[mehmetcik]

Feel free to come back to me at any time.

That is the only way to learn.