find many based on a certain criteria

[Trevor3007]

good evening,

I hope someone can help me?

I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet 2 that contains a specific word ( ie Adobe)

This would than display the data on sheet 1. A bit like vlookup, but vlookup as you are aware only picks up one line ...
I need this to display anything with the specific word on sheet 1 row by row.

IE
sheet 1 cell a2 adobe

sheet 2 would then find all the data that contains 'adobe' and display the contents of that row(s) into sheet 1 (a3:z1000)

thank you for your time
KR
Trevor3007

 

[Trevor3007]

Glad you sorted it & thanks for the feedback

NP Fluff & its justly deserved!

However, I have put this VB etc into the live workbook &it has 10 worksheets. I tried to sort myself but failed. Could you please sort/ tweak this for me?

MTIA

 

[Fluff]

In what way does it need changing?

 

[Trevor3007]

hi,

As the sheets (3 - 10 ) are not in your VB , I get a red circle with a X and 400 to the right of the cross.

link to the file in question:-

https://1drv.ms/x/s!AvGGXsEtXRpdhoNtvYpwb0-8LFrM2Q?e=7naOhF

 

[Fluff]

You still haven't said how you want it changed.

 

[Trevor3007]

sorry fluff ,

I need to do on sheets 3-10 as it does on sheet 2 (pcode). So when I put 'milk' in 'search' b1 , it will look to pcode and the all the other worksheets & display the applicable into the 'search' worksheet.

Pretty much the same as before bit extend the search for sheets 3 - 10.

 

[Fluff]

Ok, how about

Sub Trevor3007()
    Dim Ws As Worksheet
    Dim Ary As Variant, Nary As Variant
    Dim r As Long, c As Long, cc As Long, nr As Long
    Dim Srch As String
    
    Srch = Sheets("Sheet1").Range("A2").Value
    For Each Ws In Worksheets
        If Ws.Name <> "Sheet1" Then
            Ary = Ws.Range("A1").CurrentRegion.Value2
            ReDim Nary(1 To UBound(Ary), 1 To UBound(Ary, 2))
            
            For r = 2 To UBound(Ary)
                For c = 1 To UBound(Ary, 2)
                    If InStr(1, Ary(r, c), Srch, vbTextCompare) > 0 Then
                        nr = nr + 1
                        For cc = 1 To UBound(Ary, 2)
                            Nary(nr, cc) = Ary(r, cc)
                        Next cc
                        Exit For
                    End If
                Next c
            Next r
            Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(nr, UBound(Ary, 2)).Value = Nary
        End If
    Next Ws
End Sub

 

[Trevor3007]

Thanks Fluff...
I cannot test ATM but just wanted to thank you.
Will update you once I have tested.

Have a great weekend.
KR
Trevor3007

 

[Trevor3007]

Morning Fluff,

My impatience/ curiosity got the better of me & I had just had to give it a test drive.
I have attached the test file for you.

https://1drv.ms/x/s!AvGGXsEtXRpdhoNvWzbor0Mur0VQ0g?e=P3TmWZ

Issues

1. Don’t insert any data into sheet 1 & press the ‘search’ button. Data is displayed. It should return ‘No Results’ and sheet one should remain blank.
2. Insert applicable data in a1, sheet 1 it only returns the data in sheet 2. It should display all applicable data from sheet 2,3 etc.
3. Sheet 1 , a1, it should display all relevant data within the search word that is in all the worksheets.

I am so sorry to burden this , but I am sure you can sort. I am as always very much appreciated for your help.

 

[Fluff]

How about

Sub Trevor3007()
    Dim Ws As Worksheet
    Dim Ary As Variant, Nary As Variant
    Dim r As Long, c As Long, cc As Long, nr As Long
    Dim Srch As String
    
    Srch = Sheets("Sheet1").Range("A2").Value
    If Srch = "" Then Exit Sub
    For Each Ws In Worksheets
        If Ws.Name <> "Sheet1" Then
            Ary = Ws.Range("A1").CurrentRegion.Value2
            ReDim Nary(1 To UBound(Ary), 1 To UBound(Ary, 2))
            
            For r = 2 To UBound(Ary)
                For c = 1 To UBound(Ary, 2)
                    If InStr(1, Ary(r, c), Srch, vbTextCompare) > 0 Then
                        nr = nr + 1
                        For cc = 1 To UBound(Ary, 2)
                            Nary(nr, cc) = Ary(r, cc)
                        Next cc
                        Exit For
                    End If
                Next c
            Next r
            Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(nr, UBound(Ary, 2)).Value = Nary
            nr = 0
        End If
    Next Ws
End Sub

 

[Trevor3007]

morning,
Thank for your response. I am still having issues :{

can you please check out via the link below:-

SearchCat_error.xlsm

Notice that the search starts in b1 & the tabs are renamed too. Unsure if on/both are causing the issue, but even in it original code, it still does not ( well appears) not to search & display all the worksheets up to sheet 10.

I am do hope you can sort foe me & hope you have a good day.