Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004

    Unanswered: Search Box Needed

    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

    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!
    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)
    End If
    Next r
    If ifFound = False Then MsgBox "Not Found!"
    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.
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2004
    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.
    Attached Files Attached Files
    Last edited by rhinoball; 09-01-11 at 12:44.

  3. #3
    Join Date
    Jan 2002
    Bay Area
    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.
    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
        If j = 2 Then
            MsgBox "No data found for the target " & strTarget
        End If
    End Sub
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts