Search multiple worksheets for value entered in input box

miff3436

New Member
Joined
Apr 20, 2012
Messages
33
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,099
Office Version
  1. 2019
Platform
  1. Windows
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.

Code:
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

New Member
Joined
Apr 20, 2012
Messages
33
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,099
Office Version
  1. 2019
Platform
  1. Windows
Try changing this line:

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

to:

Code:
r=2

Dave
 

miff3436

New Member
Joined
Apr 20, 2012
Messages
33
Hi dmt32

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

Thanks for your time
miff3436
 

Watch MrExcel Video

Forum statistics

Threads
1,109,204
Messages
5,527,405
Members
409,760
Latest member
zeeshansyed

This Week's Hot Topics

Top