Excel Search Function - Help

[falkon007]

Hi there,

I am wokring on adapting a VBA someone else wrote to apply it to my excel workbook.
So far I have it working to display the results of all the feilds that match the text with "C6" or (6,3).

Here is the code:

Please Login or Register  to view this content.

So all the cells within worksheet "EA" within range of AK2: AK(however many rows I have) are displayed in Cell D24 and down. (I just blatantly copied this from someone else and tweaked a few values to fit my spreadsheet).

The results that match are pasted in D24 (and down) and the value in the cell to the immediate right of the matched (i.e. if AK2 in EA matched C6 in my search worksheet, then AL2 is the one to the immediate right) is pasted into the worksheet in C24 (one position to the left). Opposite direction...

Im guessing it is something to do with:

Please Login or Register  to view this content.

What I would like is almost there... but I do not know how to tweak it.

How would I be able to make the value matched to be pasted in to C24 and then the next 6 cells to the right of the matched cell (e.g. AK2 in "EA" worksheet) to be pasted in to D24,E24,F24,G24,H24,I24 respectively?

The original spreadsheet that I copied this from is attached.

p.s. My worksheet has 470000 rows and 140 coloumns and every 2nd or 3rd time I open the document and run this macro it says that it is out of memory and cannot perform the function... (if any one has hints on helping excel deal with larger amounts of data let me know...but this is less improtant as I can easily split this data up later).

p.p.s. If anyone has a good tutorial series on youtube or a few books they would recomend to learning how to deal with Search Arrays (or whatever you call these types of VBAs) in Excel VBA and learn all these terms such as UBound, (1 to 2), reDim etc.... I would also appreciate it...Ideally I would like to give back one day to this forum and help others

[AB33]

I do not know about your code as your sheet names and columns have changed, but on the above attached which is written by Mike7952, this is how the matched data have been copied.
There are two columns only. Column A is displaying the result from the data sheet of column A(Material heading). That part of the code is:

Please Login or Register  to view this content.

And the second column which is B is displaying the data from Column B of sheet data (Material description, heading). That part of the code is:

Please Login or Register  to view this content.

I am not sure I understand your request clearly, but if you want to copy the data in vertical rather than horizontal form which is how the data are displayed at the moment, you can change this line of the code.

Please Login or Register  to view this content.

INTO

Please Login or Register  to view this content.

This means all previous column A data will be displayed horizontally.

[falkon007]

Sorry for being unclear before.

First of all thank you for crediting the original author Mike7952.

Second, what I was asking was imagine if Mike's "Data" worksheet had additional columns such as Coloumn "C" was Year of manufacture, Column "D" was colour, Column "E" was Sub atomic weight :p etc.

How would I rewrite this to still compare what he has put in his search box with coloumn "B" (as it is now), but when the results are pasted in the "Search" sheet it would show all adjacent columns.

e.g. I put the "BELT V INDUS STD A60 SET/5" in the search box. When I press Search it would show:
A7 as 345.
B7 as BELT V INDUS STD A60 SET/5
C7 as 1993
D7 as Magenta
E7 as 200000 micro grams etc.

Do you understand what I am saying?
Just trying to adapt this so the search show more columns in teh order they appear on the other sheet. I do not want to change the display from horizontal to vertical or visa versa.

Thank you in advance.

[AB33]

Well, it can be done, but will be tedious work using an array. As it is now, the array is returning two columns only, but you want to expand it in to many columns.

Please Login or Register  to view this content.

needs to be expanded to include all adjust columns using an array.
If you want similar code but flexible code, please attach a sample, so that it makes like easier to see the format of your data.

[falkon007]

Well, it can be done, but will be tedious work using an array. As it is now, the array is returning two columns only, but you want to expand it in to many columns.

Please Login or Register  to view this content.

needs to be expanded to include all adjust columns using an array.
If you want similar code but flexible code, please attach a sample, so that it makes like easier to see the format of your data.

Adjusting the code is what I do not know how to achieve. To expand the array to return more than two columns. I tried the brute force method by changing the (arrPart, 2) to arrPart, 3) but that did not work.

Anyway I have re-attached the excel with extra columns of information in the DATA worksheet and have put the heading in the "Search" worksheet where I would like the result to appear.

Does it make sense?

I would appreciate any guidance on this.

If you know of a smarter method or less memory intensive VBA I would appreciate it. But if we could get this working it would be amazing.

Kind Regards.

[AB33]

See attached. I have adjusted your heading to reflect the new heading. This code is easy to understand and can easily adapted by changing the resize

[falkon007]

Wow AB33. Thank you so much. That VBA is inspired
It works!!

+ Rep for you sir!

May I ask, where/how did you learn to come up with such an elegant solution so quickly? I would love to get some training in my spare (although limited) time.

[AB33]

Falk,
Thanks for Rep and you are welcome!
Well, by tried to adjust Milk's code definitely puts you on higher gear. If you could understand someone else's code, I believe you are half through the battle. I have learned right in here. When I joined the forum, my knowledge was big Zero.
The best was to learn is "by doing". IMO, do not waste your time and money on course and books. Your biggest enemy as you said is "Time"

[falkon007]

Falk,
Thanks for Rep and you are welcome!
Well, by tried to adjust Milk's code definitely puts you on higher gear. If you could understand someone else's code, I believe you are half through the battle. I have learned right in here. When I joined the forum, my knowledge was big Zero.
The best was to learn is "by doing". IMO, do not waste your time and money on course and books. Your biggest enemy as you said is "Time"

Wise words Will definetly try to learn by Osmosis in here.

One question on your code.

How do I adjust is to start pasting the result in cell C24 for example on "search Sheet instead of A7?


If I change:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Then it goes all skewed...

Any Idead how to move the table down to start at C24 instead of A7?

Cheers

[AB33]

Folk,
Please do not reply with quote, just reply will do.
Two lines of the code

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I am assuming you have data in C23, or not empty, so once you clear the data, you have row 24 as the next empty cell.

[falkon007]

OK sorry no more quoting:
I have adapted my code for another spreadsheet like so:

Please Login or Register  to view this content.

In my search worksheet I only have the word "Index" typed in to Cell C23. and no other text in coloumn "C" except for the search term in C6.

When I click search it displays only the last row that matches the search in C6. In reality I have about 5 cells in column 37 that matches what I type in to the search criteria. Coul dyou give me a hint as to what may be causing this?

In truth I do not fully under stand:

Please Login or Register  to view this content.

and especially the "C" & Rows.Count).End(xlUp).Offset(1) part...?

[falkon007]

<bump> sorry about that Im just worried the solved tag may mean no one comes to help.
Should I start a new thread?

[AB33]

Falk,
Change these lines of the code

Please Login or Register  to view this content.

Please Login or Register  to view this content.

You are searching C6 value in column K and if there is a match, you want to go back to column A and copy the next 8 columns to columns C. The copied cells go to next empty column C, so you should have a header in column C23, otherwise the code will keep copying on the same row again and again.

[falkon007]

I just came on here to say I got it to work by changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and it worked.

Is there something wrong with this method?

Just curious?

[AB33]

Please Login or Register  to view this content.

was the original format. The search column was B and you want to copy from column A, so you need to go 1 to the left from B so as to end up in A, hence -1. It all depends where exactly you are on the search column and adjust the code accordingly. No, there is nothing wrong with your method

[falkon007]

Ah yes it is all begining to make sense. Now I just need to make sense of how you managed to loop through each line using :

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

and I'll be golden.

Just trying to logic it out myself slowly.

I have one last (hopefully) question to ask you if you have the patience...

In the above search VBA is there anyway I can exclude the first row of the sheet it is interrogating? In other words I do not want to show the "header"/"Column Title" which is located in Row 1 on the "DATA" sheet if it matches what you are searching for.

[AB33]

This is going to my last one, otherwise, we will be on this thread until dooms day.

Please Login or Register  to view this content.

[dados]

Can you please attach your fixed file here.. I need same thing I try to fix it but with no success... Thanks

[AB33]

Dados,
Welcome to the forum!
You need to start your own thread.
Please read forum's rules