Complicated search

[Craig1]

Hi Guys,

I need to carry out a search in column H. In H8 I want to input a criteria and have an auto filter that will filter the info I input
The problem is the column is populated with machinary parts numbers, something like GBQ2-LP04-00358-358RE401. This number cannot be remembered very easily so most people remember the last set of numbers / letters.
Using a command button I would like to search for the last 8 (in this case) letters / numbers so it would be 358RE401.
Then a problem arises because the next search I need to do may be GBQ2-GP04-0G405-304FBD461 which is now 9 letters or numbers.
It may simplify it by counting the 304FBD461 that I enter in H8 and then bring all the GBQ2-GP04-0G405-304FBD461 in the auto filter by me just entering 304FBD461.

Not sure I have explained this 100%, so any help / questions would be appreciated.

Craig.

 

[shawnhet]

May I suggest a workaround here?

The following should return the last grouping of numbers(assuming that they are already delimited by "-")

=RIGHT(A1,LEN(A1)-SEARCH("@",SUBSTITUTE(A1,"-","@",LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))))

Then you can copy that formula downwards and filter by the new column.

Then it looks as though you are looking for a macro to actually perform the filter - Note - untested code --

.AutoFilterMode = False

            .Range("A9:I100").AutoFilter

            .Range("A9:I100").AutoFilter Field:=9, Criteria1:=Range("H8").Value

I am assuming the search field is in H8 and the previously provided column is in column I - you will have to adjust the range to be filtered(currently A9:I100) and most likely assign a shortcut etc.

Cheers,

 

[Craig1]

Thanks Shawn, eventually got it all working a treat.

Thanks again.

Craig.