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

 

[Fluff]

Change this Ary = Ws.Range("A1").CurrentRegion.Value2to Ary = Ws.UsedRange.Value2

 

[Trevor3007]

thanks fluff...

sorry am still getting an issue

see screenshort above.

MTIA

 

[Fluff]

You need to change this line Ary = Ws.Range("A1").CurrentRegion.Value2 to read Ary = Ws.UsedRange.Value2 not add them together.

 

[Trevor3007]

many thanks...

I hope I have correctly inserted the applicable correctly 9see BOLD txt)?

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("search").Range("b1").Value
    If Srch = "" Then Exit Sub
    For Each Ws In Worksheets
        If Ws.Name <> "Search" Then
           [B] Ary = Ws.UsedRange.Value2[/B]
            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("search").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(nr, UBound(Ary, 2)).Value = Nary
            nr = 0
        End If
    Next Ws
End Sub

(1) When I run a search with a specific word it returns:-

(2) if i run a search which is on sheet10, it returns:-

It should be that when a search word is placed, that it looks within the wholework book and displays any word that contains the 'search' word , ie *hello*

MTIA

 

[Fluff]

Part of the problem is that soem of your sheets are blank.
Try

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("search").Range("b1").Value
    If Srch = "" Then Exit Sub
    For Each Ws In Worksheets
        If Ws.Name <> "Search" Then
            Ary = Ws.UsedRange.Value2
            If Not IsEmpty(Ary) Then
                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
            End If
            If nr > 0 Then Sheets("search").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(nr, UBound(Ary, 2)).Value = Nary
            nr = 0
        End If
    Next Ws
End Sub

 

[Trevor3007]

hi,

nearly there :}

just seems to be the same as issue as before where it does not search all the worksheets (see below)

 

[Fluff]

Step through the code using F8 to see where it fails & what the error message & number are.

 

[Trevor3007]

hi,

presing f8 , highlights each line in yellow, but does NOT bring up a error!!! What happens when you tested it?

 

[Fluff]

It works for me without any problem.
Did you keep stepping through, until the macro finished?

 

[Trevor3007]

good morning Fluff,

thank you for your ongoing assistance.

I ran the macro & f8, which displays :-

this is what search criteria iused:-

link below has the applicable workbook:-

Searchissue191119

hope you can sort & MTIA