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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,320
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
7,320
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,718
Messages
5,833,289
Members
430,202
Latest member
Faizal5zl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top