Search Data macro question for mike7952

Hello there, I have a question for mike7952.

I have a question about the Search Data.xlsm file that he posted in post 12 found here:
http://www.excelforum.com/excel-prog...rd-phrase.html
which is outstanding by the way.

My question is, how do I expand the search to include more columns in the Data worksheet, and then have that data show up in the search results in the Search worksheet? I'm not sure at this point how many columns I'd like to search in the Data worksheet (we'll say A through H at this point), but in the future, if I want to search more columns, how would I expand the search to include more / less columns?

Thank you!

Hi
I have just had a look at the code contained in your link and as far as I can see all you need to do is change the second column of the range in this line:

Set rngParts = ws.Range("B2:B" & ws.Cells(Rows.CountLarge, "B").End(xlUp).Row)

For example if you last column in the Range was Column O then change the "B2:B" above to "B2:O".

I have tested it here and it seems to work okay.

Hope this helps.
Good luck.
Tony

Tony,
I have seen this code pops up frequently and I have adjusted it few times. It has arrays. I do not see any benefit using an array for this task as the search value is a single item. Arrays are good when the speed and volume of data are the issues, but not in this case. Arrays are not flexible unlike using ranges.

  arrPart(1, UBound(arrPart, 2)) = FoundCell.Offset(0, -1)
        arrPart(2, UBound(arrPart, 2)) = FoundCell.Value
        ReDim Preserve arrPart(1 To 2, 1 To UBound(arrPart, 2) + 1)

The code copies two columns only.
If we use ranges, you can easily expand/contract the copied range using resize method.

Thanks ARGK and AB33.

ARGK, if I use this line of code:

Set rngParts = ws.Range("B2:O" & ws.Cells(Rows.CountLarge, "B").End(xlUp).Row)

How would I get the results of the additional columns to appear in the Search worksheet? For example, on the Data worksheet if I enter in the word Test in cell O2, and then go to the Search worksheet and search for Test, the word Test appears under the Description heading and not over in column O.

Thanks!

Hi
In that case you need to contact the owner of the original code as he will know his code far better than I would.
Tony

Alternative code

This code is flexible in a sense right now it copies 12 columns, but can easily adjust it.

Sub x()
  
Dim rFind As Range, sFind, sAddr As String, ws As Worksheet, rng As Range, ms As Worksheet
Application.ScreenUpdating = 0
Set ms = Worksheets("Search")
ms.Range("A7:Z" & Rows.Count).ClearContents
sFind = ms.Range("B2")
Set ws = Sheets("Data")
        With ws.Columns(2)
            Set rFind = .Find(sFind, .Cells(.Cells.Count), xlValues, xlPart)
            If Not rFind Is Nothing Then
                sAddr = rFind.Address
                Do
                    rFind.Offset(, -1).Resize(, 12).Copy ms.Range("A" & Rows.Count).End(xlUp).Offset(1)
                   
                    Set rFind = .FindNext(rFind)
                Loop While rFind.Address <> sAddr
                sAddr = ""
            End If
        End With
Set ms = Nothing
Set ws = Nothing
Set rng = Nothing
Set rFind = Nothing
Application.ScreenUpdating = True
End Sub

Hi AB33, thank you kindly. If I use your code as a macro in the Search Data.xlsm file, how would I get the results on the Data worksheet from column 0, for example, to appear on the Search worksheet in column 0?

Currently, the search is on sheet search B2 and the result is copied over to sheet data in column A. Which one do you want to change?

In the attached workbook, on the Data worksheet, I would like A2:O39 to be searchable. So, if I go to the Search worksheet, and enter in TESTG12 in cell B2 and hit Search, I'd like the entire row 12 from the Data worksheet to appear in the search results.

That is easy to adjust. I believe this code is so flexible that you do not need to understand arrays to adjust it.

Excellent, that is exactly what I was looking for! Thank you AB33!