Search Multiple Columns and Rows - Show results in Multiple columns and rows

Please Login or Register  to view this content.

Looks like it fills in just fine.

Make sure you change the assigned macro to the button to my macro and not what you already have coded in the book.

I checked the button and it wasn't attached to the macro. You need to assign it.


I've added a new workbook with the button removed, so the macro runs when you change B2, and with code to clear the area each time.

For what search result? Every search I've typed in has refreshed the correct amount of data.

Hi daffodil11,
. Sorry if I am talking rubbish.. ... I had noted this thread as one to answer, but you beat me to it.. So I thought I would steal your code anyway...
. I could not get it to work either. I was very confused how you were referencing your ranges -- ( Looked like the way Rory usually moans at me for if I do it that way in threads.. ).
. So I “over did it “ a bit -- - referencing the ranges fully, like in the code below, and then I got your code to work..
.
. I expect your code works as well in the form you have it, but I am implementing it wrong or there is something else more subtle about the way you are referencing the ranges that i do not know about yet??. Can you enlighten me?
Thanks

Code that works for me:

Please Login or Register  to view this content.

Alan

The code in the most recent workbook features a Worksheet_Change event, so all sheet references are implied to the sheet the code is imbedded on.

Please Login or Register  to view this content.

Hi, Thanks for the reply, appreciate it..

Originally Posted by daffodil11

The code in the most recent workbook features a Worksheet_Change event, so all sheet references are implied to the sheet the code is imbedded on.....

.. I think I get that.. but this bit (In both your codes ) is still confusing me

.Range("A" & i, Sheets("Data").Range("M" & i))

Alan

...... accepting the bit about inmplicitly referencing the sheet in which the code is in, I would expect i t to look lkie this


Sheets("Data").Range(Sheets("Data").Range("A" & i), Sheets("Data").Range("M" & i)),

or if you were referencing the search sheet ( you are not ) then it would look like ( i thought)

.Range ( .Range("A" & i) , .Range("M" & i))

Originally Posted by heykeighley

Alan,
could you attach a file to show this your way?

.. I posted the code that works for me with your file in post #10, but wait to see what daffodil11 says, I may have missed something subtle ( he has a much better idea then I do )

You certainly can use: Range(Range("a1"),Range(xx))

However, this works just as well: Range("a1",Range("xx")).

I went through the code and applied explicit references throughout and found it included a few line of Data that were previously being omitted, particularly when searching for "RIA" it was skipping that Contract.

Please Login or Register  to view this content.

The code logic:

First it clears the paste-to area
Next, it looks at each line of Data
Of each line, it looks at each cell in each line. If a single cell contains the Search parameter of B2, it stops searching, and copies the line over
It then resumes with the next line

Originally Posted by daffodil11

You certainly can use: Range(Range("a1"),Range(xx))

However, this works just as well: Range("a1",Range("xx")).

.....

.. Thanks I have learnt something there, .I did not know you could do that.

Alan

P.s. I think , though, the original problem is that the other code of yours were referrencing the search sheet and not the data sheet. ( - as you rightly said the code was in search sheet, so implicitly you were referrencing that ) ....Which you have corrected with

wsData.Range("A" & i, wsData.Range("M" & i))

Yeah, sorry guys. I'm a sloppy coder. I hear you get better with practice.

Originally Posted by heykeighley

So is there an updated sheet that searches A thru M and returns any keyword hit? essentially this should act like a database.

What ???

Do you still need help,in getting daffodil11's latest code working??

Alan