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
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
Microsoft Excel
Last Comment
[ fanpages ]
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks so much...will test it later today !
ASKER
fanpages, Losing about 4 secs on speed. but it is working..
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.
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.
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.
Accepted answer: 0 points for fordraiders's comment #a39722571
for the following reason:
appreciate all the help on this issue from the beginning.
[Object] button used.
I believe that my own comment ("ID: 39721094") should be the accepted comment.
I believe that my own comment ("ID: 39721094") should be the accepted comment.
ASKER
Thanks for the help
You're very welcome :)
ASKER
fanpages, One small issue..
If on the Sheet i have "Barnes" or "barnes" instead of "BARNES"
its bubbling up correctly
Sorry.
fordraiders
If on the Sheet i have "Barnes" or "barnes" instead of "BARNES"
its bubbling up correctly
Sorry.
fordraiders
Err... If it is "bubbling up" correctly, does that mean you have a problem, or you don't?
ASKER
sorry, its NOT bubbling up correctly.
ASKER
you are elite..very good...Thanks for all the extra help...Happy Holidays !
Best wishes to you, too :)