Search for all entries containing the searched value

[Crdtk8]

Hello all,
So I'm working with a big worksheet containing delays, these delays are all assigned a code based off of the reason for delay. I'm trying to add a search feature that searches my initial sheet "Raw Data" for all entries of the ata code and than displays them on a new sheet "Output". Below is the code I found and modified to meet my specific needs, but I can't seem to make it keep searching for all entries as opposed to just the initial entry when the code appears. All help is greatly apperciated!


Sub SearchForNumber()
Dim Search As Variant
Dim msg As String, msg1 As String
Dim r As Long
Dim c As Range, Rng As Range
Dim wsOutPut As Worksheet, sh As Worksheet




Set wsOutPut = Worksheets("Output")
'Open inputbox
Top:
msg = ""
Do
Search = InputBox("Enter Search Number Value:", "Search")
If StrPtr(Search) = 0 Then Exit Sub
Loop Until IsNumeric(Search)




Application.ScreenUpdating = False




r = wsOutPut.Cells(wsOutPut.Rows.Count, "A").End(xlUp).Row + 1




For Each sh In Worksheets(Array("Raw Data"))
With sh
.Activate
Set c = .Columns(5).Find(What:=CLng(Search), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not c Is Nothing Then
Set Rng = .Rows(c.Row)
wsOutPut.Rows(r).Value = Rng.Value
r = r + 1
msg = msg & "Sheet: " & sh.Name & " - Record for " & Search & " found." & Chr(10) & Chr(10)
Else
msg = msg & "Sheet: " & sh.Name & " - Record not found!" & Chr(10) & Chr(10)
End If
End With
Next sh




Application.ScreenUpdating = True
msg = MsgBox(msg & Chr(10) & "Do you want to make another search?", 36, "Results")
If msg = 6 Then GoTo Top
End Sub

 

[Wessie]

Hi

Could you just filter for the code and copy that data to a new sheet?
Or use a Pivot table.

Let me know if you need help with this.
The coding above seems too complex for a simple task like this.

Regards,
R

 

[Crdtk8]

Hi

Could you just filter for the code and copy that data to a new sheet?
Or use a Pivot table.

Let me know if you need help with this.
The coding above seems too complex for a simple task like this.

Regards,
R

That would technically work, except I'm not making this feature for myself, I'm making it for my superiors and his superiors and I'm attempting to make it "dummy proof". I have this macro attached to a push button thats displayed on another sheet on this workbook, and all I'm attempting to do it make it so if they wish to see delays for ata code 8000, they click the button, type in 8000, and all the delays for this quarter are displayed on the new sheet for them to easily look into.

I've got the code to do almost exactly what I want, I just need it to display every instance 8000 occurs, not just the first

 

[Crdtk8]

Hi all,
So I hate searching through a forum only to find exactly the question I have, and have it still be unsolved, which sadly is the nature of the beast. I solved the problem with my own code, so I'm posting it here for future use for anyone else looking to do the same.

This vba code searches through my entire initial sheet "Raw Data", it looks through column E which contains my ATA delays (a #), it finds every instance of this delay, and than posts it to a seperate sheet called "OUTPUT", this allowed me to send this sheet out to my higher ups and effectively make it a higher level of "dummy proof". This vba utilizes a inputbox so you can actively change whatever it is that you're searching for, I tried to explain the code out the best i can here and inside itself, hope this helps someone else in the future.


Sub SearchForATA()


Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim LSearchValue As String

On Error GoTo Err_Execute

LSearchValue = Application.InputBox(Prompt:="Please enter a ATA to search for.", Title:="Display all ATA entires in OUTPUT")

Application.ScreenUpdating = False

'Start search in row 2
LSearchRow = 2

'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2

Sheets("Raw Data").Select

While Len(Range("A" & CStr(LSearchRow)).Value) > 0

'If value in column E = "ATA", copy entire row to Sheet2
If Range("E" & CStr(LSearchRow)).Value = LSearchValue Then

'Select row in Raw Data to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into OUTPUT in next row
Sheets("OutPut").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Raw Data to continue searching
Sheets("Raw Data").Select

End If

LSearchRow = LSearchRow + 1

Wend

'Position on cell A3
Application.CutCopyMode = False
Range("A3").Select

Sheets("OUTPUT").Select
MsgBox "All matching data has been copied."

Exit Sub



Application.ScreenUpdating = True

Err_Execute:
MsgBox "An error occurred."

End Sub