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

[heykeighley]

I need some help with a project. I`m trying create a searchable spreadsheet that will search multiple columns and rows from one sheet and return matched data to another sheet. - For example Search the term "Vang" and everything associated with that key search is returned (Columns a-m) is this possible?

[daffodil11]

Sub KYKY()

For i = 2 To Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
    For Each rngCell In Range("A" & i, Range("M" & i))
        If InStr(1, rngCell, Sheets("Search").Range("B2")) > 0 Then
            Sheets("Data").Range("A" & i, Sheets("Data").Range("M" & i)).Copy Sheets("Search").Range("A" & Rows.Count).End(xlUp).Offset(1)
            Exit For
        End If
    Next
Next

End Sub

[heykeighley]

I added that but I think I messed up, its returning information from the data sheets in columns c-m .....also i`d like to have it show all rows of a product.

[daffodil11]

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.

[heykeighley]

Clearly i`m an idiot, when I open the workbook- enable micros and run a search it will A) Only search keywords in Column B on the data page B) will only display data for C-M once and stay static regardless of what other key words I search for. C) not sure how to properly assign the micros to stay.

[heykeighley]

Still not sure why the updated KYKY macro wont run every time I enter a search term

[daffodil11]

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.

[Doc.AElstein]

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:

Sub KYKYFullRangeReferrencing()
For i = 2 To Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
    For Each rngCell In Sheets("Data").Range(Sheets("Data").Range("A" & i), Sheets("Data").Range("M" & i))
        If InStr(1, rngCell, Sheets("Search").Range("B2")) > 0 Then
            Sheets("Data").Range("A" & i, Sheets("Data").Range("M" & i)).Copy Sheets("Search").Range("A" & Rows.Count).End(xlUp).Offset(1)
            Exit For
        End If
    Next
Next
End Sub

Alan

[heykeighley]

Its only returning data from Data sheet B:2 Thru M:2

[daffodil11]

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

[heykeighley]

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

[Doc.AElstein]

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 )

[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.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Target.Address <> "$B$2" Then Exit Sub
Application.EnableEvents = False

Range("A" & 7, Range("M" & Range("A" & Rows.Count).End(xlUp).Row)).ClearContents

For i = 2 To Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
    For Each rngCell In Range("A" & i, Range("M" & i))
        If InStr(1, rngCell, Sheets("Search").Range("B2")) > 0 Then
            Sheets("Data").Range("A" & i, Sheets("Data").Range("M" & i)).Copy Sheets("Search").Range("A" & Rows.Count).End(xlUp).Offset(1)
            Exit For
        End If
    Next
Next

Application.EnableEvents = True
End Sub

[Doc.AElstein]

Hi, Thanks for the reply, appreciate it..

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))

[heykeighley]

Its still not returning the correct data.

[heykeighley]

See Attached- example search Jackson - it doesnt return correct

[daffodil11]

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.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Target.Address <> "$B$2" Then Exit Sub
Application.EnableEvents = False

Dim wsSearch As Worksheet: Set wsSearch = Sheets("Search")
Dim wsData As Worksheet: Set wsData = Sheets("Data")

wsSearch.Range("A" & 7, wsSearch.Range("M" & Range("A" & Rows.Count).End(xlUp).Row)).ClearContents

For i = 2 To wsData.Range("A" & Rows.Count).End(xlUp).Row
    For Each rngCell In wsData.Range("A" & i, wsData.Range("M" & i))
        If InStr(1, rngCell, wsSearch.Range("B2")) > 0 Then
            wsData.Range("A" & i, wsData.Range("M" & i)).Copy wsSearch.Range("A" & Rows.Count).End(xlUp).Offset(1)
            Exit For
        End If
    Next
Next

Application.EnableEvents = True
End Sub

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

[Doc.AElstein]

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))

[daffodil11]

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

[heykeighley]

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

[Doc.AElstein]

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