Search Box Needed

rhinoball · #1 (**permalink**) 08-31-11, 10:53

Hello All,

I was hoping somebody would be able to take a look at my spreadsheet and help me out with a problem I was having. I have tried to make the sheet so that I would have a searchbox in Sheet 1, that would look at the data in Sheet two and Return the results in Sheet 3.

The Search would look at Column A in Sheet 2 and Return all Instances of that Result in Sheet 3 (Including all other data in the Row).

If anyone was able to tell me the code that would help, I am very new to VB coding and had been trying to tinker with the code that I found on a website

Code:

Sub CommandButton1_Click()
'Standard Sheet Module code, like: Sheet1.
Dim ifFound
Dim Message$, Title$, Default$, myCode$

Message = "Enter Workstation/Laptop Number:" ' Set prompt.
Title = "Workstation Search" ' Set title.
Default = "" ' Set default.
' Display message, title, and default value.
myCode = InputBox(Message, Title, Default)

MsgBox "Var1: value is " & myCode

ifFound = False
Application.ScreenUpdating = False

ifFound = True

'Check data sheet for data wanted!
Worksheets("Sheet2").Select
For Each r In Worksheets("Sheet2").UsedRange.Columns
n = r.Column

If Worksheets("Sheet2").Cells(1, n) = myCode Then

MsgBox "Var2: value is " & ifFound

ifFound = True
'Copy the found data from the starting row = myStart to
'the ending row = myFinish for the Found Date [myDate]
'to Sheet2 in column "C" change as needed!
'Start data import just below any entery in this column!
Worksheets("Sheet2").Range(Cells(2, n), Cells(4, n)).Copy _
Destination:=Worksheets("Sheet3").Range("C65536").End(xlUp).Offset(1, 0)
Else

End If
Next r

If ifFound = False Then MsgBox "Not Found!"
Worksheets("Sheet3").Select
Application.CutCopyMode = True
Application.ScreenUpdating = True
End Sub

What I had managed to do so far was prove that the Data I entered was being stored in the variable but had not really got any further than that.

rhinoball · #2 (**permalink**) 09-01-11, 10:45

Hi Guys,

I have got a lot further with this after checking some other code on the internet and have somehow managed to come up with the spreadsheet attached, at the moment, on opening the sheet, it will bring up the box to search, I click in my search Criteria (eg LT00359) and then click on ok, this will copy the data into sheet 2 from Sheet 1 that matches that criteria, however this only works once. I had set another procedure to call the module code again upon reselecting the Search sheet, this works but when i put the search criteria in again (eg LT00010) it appears to run but does not do anything with the data, as soon as I close and open again it will again work once.

In fact, testing it now, that has now also stopped working, it only appears to work some of the time, is there something that I am doing wrong with this.
It seems it only appears to work reliably when I run it as the macro in the module, and not when I try to load it on events.

After a bit more testing, I have set the macro to run off a button from Sheet 1, Search sheet itsellf has now gone, and this does work everytime, however I have been unable to edit the macro so that it clears the data in sheet 2 before it runs again so that If the second search has less data, it does not keep the old data below it on Sheet 2. Is anybody able to assist with this.

JerryDal · #3 (**permalink**) 09-08-11, 19:25

Attached is my Excel VBA solution to this post. From Sheet1, it searches Sheet2 for the entered target, and adds found target data, 3 columns in my example, to Sheet3. It assumes Sheet2 has no blanks in column A within the data rows. I hope this will help.
Jerry

Code:

Private Sub cmdSearch_Click()
Dim strTarget
Dim i As Integer, j As Integer, rowCount As Integer
Dim arrValues As Variant

    strTarget = InputBox("Enter the search target")
    
    If strTarget = "" Then Exit Sub

    rowCount = Sheets(3).UsedRange.Rows.Count
    'clear all but the heading row on Sheet3
    If rowCount > 1 Then Sheets(3).Range("A2:C" & rowCount).ClearContents
    
    i = 2       'or set i to the appropriate starting data row for Sheet2
    j = 2       'starting data row on Sheet3
    
    While Sheets(2).Cells(i, 1) <> ""
        If CStr(Sheets(2).Cells(i, 1).Value) = strTarget Then
            
            'put values from 3 columns on Sheet2 into array
            arrValues = Sheets(2).Range("A" & i & ":C" & i).Value
            
            'put the 3 array values in columns A,B,C in Sheet3
            Sheets(3).Range("A" & j & ":C" & j) = arrValues
            
            j = j + 1
        
        End If
        i = i + 1
    Wend
    
    If j = 2 Then
        MsgBox "No data found for the target " & strTarget
    Else
        Sheets(3).Select
    End If
    
End Sub

Internet Services

Web Development

Digital Marketing

Consumer Tech