Macro to find text and display entire row in message box

Hi all,

I've a workbook with a few sheets where column A=old partnr, B=old name, C=old E-nr, D=comment, the columns E, F, G and H is the same but for the new part.

I want to add a search box where I can search for a string, could be the name, partnr, new or old, or what ever, and if it's found I want to display the entire row in a message box, if the search string is match more than once, I want the message box to display all matches.

I hope it make sence, I appreciate any help.

Thanks

Here is the workbook, it's in Swedish but I suppose it's okej anyway :-)

Hi, The code helped me a lot on one of my application. Thanks! But the code results an error if there is no cell contains the input string. Can the code be modified to include a msgbox saying the string could not be found?

Hi there,

I had a bit spare time and made a small search form for you.
it iterates through all worksheets and searches the used rows for the given data
Attention it only searches the first 9 columns cause of the restriction for unbound listboxes. (for your data it should be fine anyway only 8 cols max)
you can also search for substrings doesnt need 100% match.
The Result is shown in a listbox together with the Adress (worksheet|Adress)

when you double click an entry in the listbox it will jump to the sheet and row where that entry was found.

Hope this helps you
have a nice day

Thanks a lot LordLoki, it works great.

Thanks again and have a nice day

Originally Posted by LordLoki

Hi there,

I had a bit spare time and made a small search form for you.
it iterates through all worksheets and searches the used rows for the given data
Attention it only searches the first 9 columns cause of the restriction for unbound listboxes. (for your data it should be fine anyway only 8 cols max)
you can also search for substrings doesnt need 100% match.
The Result is shown in a listbox together with the Adress (worksheet|Adress)

when you double click an entry in the listbox it will jump to the sheet and row where that entry was found.

Hope this helps you
have a nice day

Hi LordLoki,

Is it possible to display only selected columns (example "Type" and "MPG") and not the entire row in the listbox?
Also, if i have more than 10 columns in the worksheets, what are the changes needed in the code.

Still learning...

Thanks in advance.

Hi cklee,

Pretty much everything is possible with VBA just have to find the right workaround :D
When you stick with the 9 columns in the Listbox it is not that big of a change. I would create an array with the column numbers that you want to show and then lopp through that array instead of the whole row.
When you want more then the 9 Columns it gets a little bit more difficult cause for no good reason you can only fill the Listbox with 10 columns if you not use rowsource to fill it.
So if you want more then 9 columns you need to create a result Sheet or put the Data in an Array.
I would go with the Result sheet cause the Array you need would be multidimensional and thats always a mess.

Attached you find an example with settings for the rows to show. Remember you can still only use 9 columns to show cause number 10 is reserved to store the adress so that doubleklick jump to entry is working.
I also moved the adress to the last column and dont show it cause it looks better

you can set your searchrange and the columns you want to be shown in this code part

Please Login or Register  to view this content.

Originally Posted by LordLoki

Hi cklee,

Pretty much everything is possible with VBA just have to find the right workaround :D
When you stick with the 9 columns in the Listbox it is not that big of a change. I would create an array with the column numbers that you want to show and then lopp through that array instead of the whole row.
When you want more then the 9 Columns it gets a little bit more difficult cause for no good reason you can only fill the Listbox with 10 columns if you not use rowsource to fill it.
So if you want more then 9 columns you need to create a result Sheet or put the Data in an Array.
I would go with the Result sheet cause the Array you need would be multidimensional and thats always a mess.

Attached you find an example with settings for the rows to show. Remember you can still only use 9 columns to show cause number 10 is reserved to store the adress so that doubleklick jump to entry is working.
I also moved the adress to the last column and dont show it cause it looks better

you can set your searchrange and the columns you want to be shown in this code part

Please Login or Register  to view this content.

Thanks LordLoki!

Eh, I'm lost here :P
This is my second attempt at VBA (expanding on what I've picked up here in the forum).
I was going through the forum and found this thread that I think I could actually use it. But too complicated for me.

In my worksheets, I should have more than 10 columns, 1/2 of which are just for reference purposes. So actual search range will be limited to about 6 - 7 columns which I would search and have them displayed.

About the address you moved, that's great! I was trying to have them removed! lol.

Now I have to figure out how it works..

Best regards.

so if you only have 6-7 rows to display you are good to go with the actual solution.
The Search Range can be as many columns as you like.
Only the Result that is shown in the listbox is limited to 9 or 10 if you dont need the double click jump to entry feature.

To make it work you just change the sc to the column where your search should start and lc to the column in which your search ends
For example Look in columns B to Z would be
sc = 2
lc = 26
And from all this columns you want to display C,D,G then you change resultcols = Array(1, 2, 3, 4, 5, 6, 7, 8, 9) to
resultcols = Array(3,4,7)

If you have more questions feel free to ask

With this method you have to use labels for the header. Cause labels only work when you use the rowsource to fill the listbox. Columnwidth is easy will post that later I'm not at the computer at the moment.

I'm living in Thurgau and work in Zürich

Gesendet von meinem HTC One mit Tapatalk

Ah Zurich. I used to visit Zug for work in the past.
Really missed those trips to Switzerland.