Search part of a string using vlookup

Hi

Would anyone be able to advise of the vb code to search a column of text for an exact match and return the row number?

I have a column of data strings such as "13ABCD00012", "13DFER00118" etc. I have a txtbox "txtPO" which contains the search stricg, say "118". With the code, I want to search the column of data strings for a string that contains "118", say. In this example, it would result in finding
"13DFER0018" and return, say, 65 as the row number in that column where the full string is.

Hope this makes sense and hope someone could advise.

Cheers

You wouldn't use VLOOKUP, you would use MATCH to get the row.

For example

=MATCH("*" & A1 & "*", B1:B100, 0)

=INDEX(B1:B100,MATCH("*"&A1&"*",B1:B100,0))

Note, these are worksheet formulas and the first one returns the row not the column.

Hi Norie

Sounds good using MATCH and INDEX. How would I use these in vba? Should I prefix with Application.WorksheetFunction?

You never mentioned VBA.

If you are searching for partial matches in VBA you might be better off with using VBA's Find method.

If it finds a match it returns a reference to the cell with the match, from which you can get the row number and value.

Sorry about that Norie I should have made it clear at the outset about wanting to use VBA. I am a novice in VBA and wonder if you could help start me off with the code?

Can you give more information?

What range in which worksheet will you be looking in?

In column A is where all Order Numbers are stored. These Order Numbers look like 13MDGH00013, 13KJYT00017, 13BMLP01076 and so on. All Order Numbers are prefixed with 13 followed 4 letters and followed by 5 numerical digits. I want to search all the Order Numbers in the column A for the one which has, say 1076 as the numerical part. In the example Order Numbers above, it would recognise the Order Number 13BMLP01076 as the one matching the search criteria and will tell me the row that this Order Number is in that column.

Does this make sense?

Yep, makes sense so far.

Now where's the textbox and what do you want to do with the result if there is a match, or even if there isn't?

The textbox (called txtPO) is on a userform. If there is no match, I want a msgbox tell me that there is no match. If there is a match, I want the code to allocate the variable CurRow with that row number.

Thanks for being patient, Norie!

Here's a small example workbook.

Might give you a start.

Norie

Your code works and give the results I would be looking for. How can your code be tweaked to input just the numbers without the zeros in the search box? So if I want to search for the Order Number 13DFER00118 I would just put 118 in the search box and the results would be it's in row 4 and the full Order Number is 13DFER00118. I suspect it would involve using left or mid string functions, but I just can't get my head around it!

Strange, when I typed 118 in and hit the button it found 13DFER00118 in row 4.

Did you have to add zeroes for it to work?

I typed 118 and the msgbox said nothing found. I tried with 00118 which also said nothing found. It only gave the result when I typed the whole Order Number, ie 13DFER00118. What am I doing wrong?

It's probably my fault.

If you look at the code you'll see the only argument I've supplied for Find is the value from the textbox.

There are various other arguments that can be used.

Try changing the code to this.

Please Login or Register  to view this content.

Norie

YEAY!!!! It worked. You have saved me days of searching! Thanks very much. Have a great weekend