Wildcard search in VBA doesn't seem to work on some data

**Amita68** · 09-21-2017, 04:17 PM

Hello

I have data which consists of part numbers with a mix of letters and digits, i.e. 4199E711 or 320/A1999 and some just numbers. To prevent what I understand to be an Excel "bug" from change this type of data to scientific format, I have forced this data to be stored as text. I believe it is the "E" between the digits that Excel seems not to like. So I now have data with lots of green triangles in the lefthand corner of some cells. however part numbers like 320/A4132 is fine. I have a userform which searches this data and populates a listbox within the userform. I have used a wildcard search criteria as part of an advance filter. This seems to work Ok but not if I search "320"... nothing is found. This is the code which I have followed as part of an online tutorial and adapted to my data (I am new to VBA):

Please Login or Register  to view this content.

Would be grateful of any help.
Regards
Amita

**Keebellah** · 09-22-2017, 04:18 PM

To prevent exponential values you van also format the cells as number with no decimals, text is not that 'user friendly'

By wild cards, which?
The vba form the userform does not help because I do not know hat textsearch is.
If you look into part of a text you could use Instr() but then that dos not work nicely with .Find.
I guess the file with dummy data and the userform and macros will be a better option to get help and a correct answer or at least put you on the right track

**Amita68** · 09-23-2017, 02:41 PM

Hello
Thank you for taking the time to read my post.
By WildCard I mean that it searches for partial data within the specified range.
I have adjusted my file to show dummy data but keeping the formats and retaining the concept. However, the file is almost 20mb and am unable to upload. Could you please suggest another method for sending the file?
Regards
Amita

**Keebellah** · 09-23-2017, 03:38 PM

You could reduce the number of rows and make sure that empty ranges are not included.
In the worksheets press Shift+Ctrl+End and it will take you to the last cell.

Check if this is really the last filled cell,
If not delete all rows form the last filled row all the way down and from the last column all the way to the right.
This will reduce your file

Without an idea of a sample and how you're searching is difficult to advice

**Amita68** · 09-24-2017, 02:34 PM

Hello
I did what you said and it has reduced the file size, thank you.
Just to give you some background. We have no system and I have been tasked to put something together where there is one place to search parts, who buys them, their alternative customer numbers, who supplies the raw casting and inventory control, etc. We are a subcontract company. We have Customers with several divisions, each division trades separately and could order the same part under different POs. Hopefully this will explain why some parts appear as duplication. The project is still work in progress and is nowhere near finished. In adjusting my data to remove sensitive data, I'm not now sure whether my original problem exists. I would however be interested in your opinion. The Parts userform searches/edits and adds parts using all search options. I'm not sure whether this is working without fault in all search options or picking out the data. The customer and suppliers forms are fine but need to be populated with address and contact info which I intend to do at a later stage. I am working on the inventory stage. This is proving the most difficult as I'm not sure how to approach this. You will see from the data sheet, that because of the mix of letters and numbers I have had to force the format to text otherwise Excel changed some info to scientific format. Again, please bear in mind I am very new to VBA and have just looked up info and copied, etc.

regards
Amita

**Keebellah** · 09-24-2017, 03:59 PM

I am using Office 2010 and Office 2016 but do not have the reference MSCOMCTL2.OCX this is probably because you are using a Office 64-bit and I am not, but I canged the fererence to MSCOMCTL.OCX to at least take a look and see what you macro's do.
One command is not recognized and that is Clear ??? it's supposed to clear search data but I do not see how Clear would know what to Clear so my guess is that this incorrect in you VBA code.
apart from that, you mention that searching for 320 doesn't do anything.
Well, it does here

Another thing, you do not need to format long numbers as text, format them as number without Decimals and then they will show as long integers.

So back to your search issue, no problem so far here.

**Amita68** · 09-24-2017, 05:16 PM

Hello
Thank you for a quick response.
This is what I feared, in adjusting my data to hide sensitive data my original problem is now gone. In my original file if I search 320 I only get one result where the line ID shows "1320" and nothing else. However if I search "320/" then all the data I expect to show does show up. I can accept with this.
You're right, the Clear sub is missing. It is a sub that clears all controls and somewhere along the line it's stopped working or "misplaced". I should be able to correct this.

With reference to the scientific format please see the link below.

HTML Code:

Thank you again for your help.

**Keebellah** · 09-24-2017, 05:41 PM

Well okay.
About the scientific notation, I do not like the apostrophe and is leaves that little green triangle at the top left.
If I know that I'll get a scientific notation and I n=know that there are no decimals I always format those cells as Number with 0 decimals even if they do contain text or like you have 320/3434242
it still works, only align then all Left so that they're all the same.

**Amita68** · 09-25-2017, 03:07 PM

Hello again.
My problem with the formatting lies with part numbers with "E" with numbers either side, i.e., 4417E221. Even if formatted as number with no decimals, I still get something other that. I agree, I don't like the green triangles, either but something I have to live with I guess.
Thanks again for looking at this for me. Much appreciated.

**Keebellah** · 09-25-2017, 03:14 PM

Here's the file with the formatting changed

What is the problem then?

**Amita68** · 09-25-2017, 04:24 PM

Try putting in 4134E071 and see what you get. It doesn't work for me.
It seems fine when there is another character or letter in addition to the E separating the digits. I have resigned to the fact this is an Excel bug. Thanks again.

**Keebellah** · 09-25-2017, 04:42 PM

Okay
Here's the result

**Amita68** · 09-25-2017, 05:38 PM

We seem to be at cross purposes. I was referring to the formatting errors for part numbers such as 4134E071 in my data sheet changing to scientific etc. Formatting this to number without decimals does not seem to work for me, the cell displays 413400000000000000000000000000000000000000000000000000000000000000000000000. Forcing this to text and searching this in the userform is not a problem. When I opened your formatted sheet, it looked fine without format errors but as soon as I enabled content the errors reappeared. I have resigned to this being an Excel issue. My problem as per my previous post is with part numbers which are automatically sorted as text without formatting errors such as 320/???? and that this did not show in the search in the userform when keying in search "320". I also said that in trying to create fictitious data this problem seems to have disappeared in the fictitious workbook and shows up in the userform but does not in my genuine workbook. I can live with this as I just need to enter a search for "320/" and not just "320"

Wildcard search in VBA doesn't seem to work on some data

LinkBack

Thread Tools

Rate This Thread

Display

Wildcard search in VBA doesn't seem to work on some data

Re: Wildcard search in VBA doesn't seem to work on some data

Re: Wildcard search in VBA doesn't seem to work on some data

Re: Wildcard search in VBA doesn't seem to work on some data

Re: Wildcard search in VBA doesn't seem to work on some data

Re: Wildcard search in VBA doesn't seem to work on some data

Re: Wildcard search in VBA doesn't seem to work on some data

Re: Wildcard search in VBA doesn't seem to work on some data

Re: Wildcard search in VBA doesn't seem to work on some data

Re: Wildcard search in VBA doesn't seem to work on some data

Re: Wildcard search in VBA doesn't seem to work on some data

Re: Wildcard search in VBA doesn't seem to work on some data

Re: Wildcard search in VBA doesn't seem to work on some data

Thread Information

Users Browsing this Thread

Similar Threads

Data validation doesn't work

[SOLVED] Work around for Run-time Error 445: Object Doesn't Support This Action (File Search)

Urgent help needed- search and replace (finden und ersetzen) somehow doesn't work properly

[SOLVED] Why doesn't a wildcard work for a workbook name compare?

Vlookup doesn't work with imported data

Excel 2007 : search data using wildcard and sum it

Data Validation doesn't work on 1 sheet only

Bookmarks

Bookmarks

Posting Permissions