Search multiple worksheets for value entered in input box

[miff3436]

Hi

I am trying to create an input box that will search for a number that is manually entered by a user. When the number is entered into the input box it will then search in Column "A" of both sheets which are named "OPEN 84" & "CLOSED 84" . When the value is found it will copy the complete row to a sheet named "OUTPUT". The number the user will enter is a unique number ( around 8 digits ) and will not return multiple values to copy. Both sheets will have approx 10-15000 rows of data.

Thanks miff3436

 

[dmt32]

Hi,
May need some adjustment to meet your specific project need but see if this code does what you want.

Place code in standard module.

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("OPEN 84", "CLOSED 84"))
        With sh
            .Activate
            Set c = .Columns(1).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

Dave

 

[miff3436]

Hi dmt32

Thanks for the quick response. Bar one small thing this is more than what i was trying to achieve ( love the search result data in the msgbox). Once the row has been found and copied to the sheet named "OUTPUT" i dont want it to find the next row down like data entry but to overwrite row 2 everytime. Sounds strange but i have the the cells in row 2 linked to textboxes in a userform and currently have hidden the excel application to stop users accessing the data.

Thanks again

 

[dmt32]

Try changing this line:

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

to:

r=2

Dave

 

[miff3436]

Hi dmt32

Perfect....Is there a way i can set this thread as solved

Thanks for your time
miff3436