asked on

Sorting userform listbox but need Instr on next enhancement

previous solution from fanpages:

https://www.experts-exchange.com/questions/28249788/sorting-listbox-with-additional-information-from-current-sheet.html

Taking a value from a sheet.

If a names exists in a column 3 of a listbox..it will bubble those results...to the top.

What I need:
What I need:

I need to see if the code can look at the current spreadhsheet and find the current activecell...
Look one cell to the left -1.
Take that value and see if it exists in Column 3 of the listbox.
(This value may appear in more than one row in the listbox...)

If results are found those rows get bumped to the top of the list.
and then the rest of the rows are still sorted the same way as requested. beneath the rows that get bumped to to the top.

The code is working fine. But sometimes the Name i'm looking for may be embededd inside the column value.

So this time if i'm looking for "Barnes"

In column 3 it may say "BARNES INC."

so if "BARNES" is within the column value..it will still bubble it to the top.

Thanks
fordraiders

ASKER CERTIFIED SOLUTION

[ fanpages ]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fordraiders

ASKER

Thanks so much...will test it later today !

Fordraiders

ASKER

fanpages, Losing about 4 secs on speed. but it is working..

[ fanpages ]

Yes, depending on the length of the strings being searched, & the quantity of strings to be searched, use of Instr(...) will slow the process down. If the string to be found was always going to be a prefix, rather than being anywhere within the test string(s), then we could make improvements on execution time.

Alternatively, we could look at using Regular Expressions (implemented via the "VBScript.RegExp" object) rather than Instr(...), but this, again, may not make any worthwhile improvements in speed due to the length & quantity of strings to be searched.

Fordraiders

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for fordraiders's comment #a39722571

for the following reason:

appreciate all the help on this issue from the beginning.

[ fanpages ]

[Object] button used.

I believe that my own comment ("ID: 39721094") should be the accepted comment.

Fordraiders

ASKER

Thanks for the help

[ fanpages ]

You're very welcome :)

Fordraiders

ASKER

fanpages, One small issue..

If on the Sheet i have "Barnes" or "barnes" instead of "BARNES"

its bubbling up correctly

Sorry.
fordraiders

[ fanpages ]

Err... If it is "bubbling up" correctly, does that mean you have a problem, or you don't?